数据库去重

查询
SELECT
*
FROM
table A
WHERE
( A.the_date, A.exponent_code, A.exponent_value ) IN ( SELECT the_date, exponent_code, exponent_value FROM table GROUP BY the_date, exponent_code, exponent_value HAVING COUNT ( * ) > 1 )
AND A.ID NOT IN ( SELECT MIN ( ID ) FROM table GROUP BY the_date, exponent_code, exponent_value HAVING COUNT ( * ) > 1 )
LIMIT 10000 OFFSET 1

删除相同指标（）
DELETE
FROM
table A
WHERE
( A.the_date, A.exponent_code, A.exponent_value ) IN ( SELECT the_date, exponent_code, exponent_value FROM table GROUP BY the_date, exponent_code, exponent_value HAVING COUNT ( * ) > 1 )
AND A.ID NOT IN ( SELECT MIN ( ID ) FROM table GROUP BY the_date, exponent_code, exponent_value HAVING COUNT ( * ) > 1 )

 

注：

A为数据表代替名，可忽略不改

the_date, exponent_code, exponent_value为表字段名，多或单个字段联合唯一

table为数据库表名

 

SELECT * FROM shopee_localize a
WHERE (a.shop_id, a.offer_id, a.product_id, a.voucher_id)
in
(SELECT shop_id, offer_id, product_id, voucher_id FROM shopee_localize GROUP BY shop_id, offer_id, product_id, voucher_id HAVING count(*)>1)
and id not in
(SELECT min(id) FROM shopee_localize GROUP BY shop_id, offer_id, product_id, voucher_id HAVING count(*)>1)