上面這條sql語句,仔細看一下,揣摩出思路也不難,大概也分為3步來理解: 根據條件,刪除原表中id大於t2中id的記錄 ...
DELETE consum_record FROM consum_record, ( SELECT min(id) id, user_id, monetary, consume_time FROM consum_record GROUP BY user_id, monetary, consume_time HAVING count(*) > 1 ) t2 WHERE consum_record.user_id = t2.user_id and consum_record.monetary = t2.monetary and consum_record.consume_time = t2.consume_time AND consum_record.id > t2.id;
上面這條sql語句,仔細看一下,揣摩出思路也不難,大概也分為3步來理解:
(SELECT min(id) id, user_id, monetary, consume_time FROM consum_record GROUP BY user_id, monetary, consume_time HAVING count(*) > 1 ) t2 查詢出重覆記錄形成一個集合(臨時表t2),集合里是每種重覆記錄的最小ID
consum_record.user_id = t2.user_id and consum_record.monetary = t2.monetary and consum_record.consume_time = t2.consume_time 關聯 判斷重覆基準的欄位
根據條件,刪除原表中id大於t2中id的記錄