根据指定列快速查询重复数据

转自：http://www.lsrxc.com/archives/613.html

–根据指定的列，快速查询重复的数据
SELECT * FROM TABLE_NAME
WHERE (COL_NAME_1, COL_NAME_2,,,COL_NAME_N) IN
(
SELECT COL_NAME_1, COL_NAME_2,,,COL_NAME_N
FROM TABLE_NAME
GROUP BY COL_NAME_1, COL_NAME_2,,,COL_NAME_N
HAVING COUNT(*) > 1
);

–根据指定的列，快速查询重复的数据（随便保留一条，需要指定一个唯一列）
–如果为删除，换成DELETE即可
SELECT * FROM TABLE_NAME
WHERE (COL_NAME_1, COL_NAME_2,,,COL_NAME_N) IN
(
SELECT COL_NAME_1, COL_NAME_2,,,COL_NAME_N
FROM TABLE_NAME
GROUP BY COL_NAME_1, COL_NAME_2,,,COL_NAME_N
HAVING COUNT(*) > 1
)
AND UNIQUE_COLUMN NOT IN
(
SELECT MIN(UNIQUE_COLUMN)
FROM TABLE_NAME
GROUP BY COL_NAME_1, COL_NAME_2,,,COL_NAME_N
HAVING COUNT(*) > 1
);

本文固定链接: http://www.lsrxc.com/archives/613.html | 异萝松的技术博客