一、MySQL删除单列重复数据保留Id最小的

需求：如表所示，其中GOODS_ID有数据重复的现象，现在要删除MER_GROUP_ID为90的重复数据

1. 里面嵌套的sql语句是找出重复列GOODS_ID中GOODS_PRICE_ID最大的数据，用SELECT语句将重复字段查找出来。

 SELECT max(GOODS_PRICE_ID) as GOODS_PRICE_ID 
        FROM GOODS_PRICE 
        WHERE MER_GROUP_ID = '90' 
        GROUP BY GOODS_ID 
        HAVING count(GOODS_ID) > 1 

2. 删除最大的GOODS_PRICE_ID，保留小的。

DELETE FROM GOODS_PRICE WHERE GOODS_PRICE_ID in (
    SELECT * FROM ( 
    SELECT max(GOODS_PRICE_ID) as GOODS_PRICE_ID 
        FROM GOODS_PRICE 
        WHERE MER_GROUP_ID = '90' 
        GROUP BY GOODS_ID 
    HAVING count(GOODS_ID) > 1) b 
    ) ; 

二、MySQL删除多列数据重复问题

需求：删除PURCHASE_ORDER_ID,STOCK_ORDER_ID,OWNER_ID多余的数据，保留STOCK_BILL_ID最小的一条

1. 先查询出重复数据

    查询出重复数据中STOCK_BILL_ID最大的 
    SELECT MAX(STOCK_BILL_ID) 
        FROM `GOODS_STOCK_BILLS` 
        GROUP BY PURCHASE_ORDER_ID,STOCK_ORDER_ID,OWNER_ID HAVING(COUNT(1)>1); 

2. 删除最大的GOODS_PRICE_ID，保留小的。

    DELETE FROM 
        GOODS_STOCK_BILLS WHERE 
        STOCK_BILL_ID IN ( 
    SELECT * FROM ( 
    SELECT MAX(STOCK_BILL_ID) FROM `GOODS_STOCK_BILLS` 
    GROUP BY PURCHASE_ORDER_ID,STOCK_ORDER_ID,OWNER_ID 
    HAVING (COUNT(*) > 1) 
        ) B 
    ); 

3. 将一张表里的某列值更新到另外一张表的某列去

on后面是行数据的唯一性，开始我只用了g.OWNER_ID = d.OWNER_ID报错了，后来把g.GOODS_ID=d.GOODS_ID加上就好了

    UPDATE GOODS_STOCK AS g
    LEFT JOIN GOODS_STOCK_DETAIL AS d ON ( g.OWNER_ID = d.OWNER_ID AND g.GOODS_ID = d.GOODS_ID ) 
    SET g.STOCK_AMOUNT = d.STOCK_COUNT 
    WHERE
    g.OWNER_ID = 'a-6b4b6e70-b66d-4739-818b-ea9e6524113b';

三、MySQL将A表某字段分组求和的数据更新到B表中另外一字段

    UPDATE GOODS_STOCK A 
    INNER JOIN (SELECT GOODS_ID,OWNER_ID,SUM(STOCK_COUNT) AS STOCK_COUNT FROM GOODS_STOCK_DETAIL GROUP BY GOODS_ID,OWNER_ID) as B ON (A.GOODS_ID = B.GOODS_ID AND A.OWNER_ID = B.OWNER_ID) 
    SET A.STOCK_AMOUNT = B.STOCK_COUNT