删除重复行

DELETE t1 FROM tmp202306253 t1
INNER JOIN (
SELECT sid, buy_date, MAX(pkId) AS max_value
FROM tmp202306253
GROUP BY sid, buy_date
HAVING COUNT(*) > 1
) t2
ON t1.sid = t2.sid AND t1.buy_date = t2.buy_date AND t1.pkId < t2.max_value;

 

pkId 是用于分辨sid 和buy_date重复的情况下比较次序的字段