mysql处理多字段有相同数据删掉只保留一条并设置联合唯一索引

-- 根据分拣机编码和统计日期删除重复的数据并值保留一条数据
-- 多加别名才能删除，因为mysql不允许自己的表查询的结果作为该表删除条件
DELETE FROM gy_log_all WHERE (sorter_code,statistics_date)
IN
(SELECT sorter_code,statistics_date FROM 
(SELECT sorter_code,statistics_date,COUNT(*) FROM gy_log_all  GROUP BY sorter_code,statistics_date HAVING COUNT(*)>1)
s1)
AND
id NOT IN (SELECT id FROM (
SELECT max(id) as id FROM gy_log_all  GROUP BY sorter_code,statistics_date  HAVING COUNT(*)>1
) s2);

-- 增加联合唯一索引
alter table gy_log_all add unique index houseIdPartId(sorter_code,statistics_date);