Mysql 删除重复字段行

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

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

GOODS_PRICE_ID	GOODS_ID	MER_GROUP_ID
1	aa	90
2	aa	90
3	bb	90
4	bb	90
5	cc	99
6	cc	98
7	dd	71
8	dd	19

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