需求:在 person 表中,id 為唯一鍵,若 id 重複即視為重複記錄,需找出所有重複的 id。
SELECT id FROM person GROUP BY id HAVING COUNT(*) > 1;
GROUP BY id:依 id 分群。COUNT(*):計算每群筆數。HAVING COUNT(*) > 1:篩選筆數超過 1 的群組,即為重複值。SELECT p.*
FROM person p
JOIN (
SELECT id
FROM person
GROUP BY id
HAVING COUNT(*) > 1
) dup ON p.id = dup.id
ORDER BY p.id;
DELETE FROM person
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM person
GROUP BY id
);