mysql 中查询重复数据

1.查询多列重复

SELECT * FROM zd_isstory_log a WHERE (a.class_id,a.lesson_id,a.course_id) IN (SELECT class_id,lesson_id,course_id FROM zd_isstory_log GROUP BY class_id,lesson_id,course_id HAVING COUNT(*) > 1) ;

2.删除重复数据（保留id小的数据）

CREATE TABLE tmp AS 

SELECT * FROM zd_isstory_log a WHERE (a.class_id,a.lesson_id,a.course_id) IN (SELECT class_id,lesson_id,course_id FROM zd_isstory_log GROUP BY class_id,lesson_id,course_id HAVING COUNT(*) > 1) 

AND id NOT IN(SELECT MIN(id) FROM zd_isstory_log GROUP BY class_id,lesson_id,course_id HAVING COUNT(*) > 1);
DELETE FROM zd_isstory_log WHERE Id IN( SELECT Id FROM tmp);