1.查找重复记录
1)查询两表的差异行:应用场景:查询增量数据
SELECT id,title
FROM (
SELECT id, title FROM t1
UNION ALL
SELECT id,title FROM t2
) tbl
GROUP BY id, title
HAVING count(*) = 1
ORDER BY id;
注意:having 的作用方对group by的每个小组应用条件
2)找本表中的重复值:找一列中重复记录
SELECT
col,
COUNT(col)
FROM
table_name
GROUP BY col
HAVING COUNT(col) > 1;
3)本表重复值:找几个组合列中重复记录
SELECT
col1, COUNT(col1),
col2, COUNT(col2),
...
FROM
table_name
GROUP BY
col1,
col2, ...
HAVING
(COUNT(col1) > 1) AND
(COUNT(col2) > 1) AND
...
2.删除重复记录
DELETE T1 FROM CONTACTS T1 INNER JOIN CONTACTS T2 WHERE T1.ID<T2.ID AND T1.E_MAIL=T2.E_MAIL;