sql - 计算表中重复行的总数

Question

我有以下 SQL 用于检查具有以下列的表中的重复行:id , case_id , raw_name , initials , name , judge_id , magistrate_id和 score .

SELECT MIN(id), case_id, initials, raw_name, count(*)
FROM my_table
GROUP BY case_id, raw_name, initials, name, judge_id, magistrate_id
HAVING count(*) > 1;

(如果一行在 case_id、raw_name、initials、name、judge_id 和 magistrate_id 列中包含相同的值，则该行被视为重复。)

如何获取需要删除的重复行总数(每组重复项保留 1 行)？

Answer 1

重复问题通常可以用EXISTS(the other)

来表示

SELECT COUNT(*)
FROM my_table mt
WHERE EXISTS ( SELECT *
        FROM my_table x
        WHERE x.case_id = mt.case_id -- exactly the same "keys"
        AND x.raw_name = mt.raw_name
        AND x.initials = mt.initials
        AND x.name = mt.name
        AND x.judge_id = mt.judge_id
        AND x.magistrate_id = mt.magistrate_id
        AND x.id < mt.id             -- but a smaller (surrogate) key
          -- If your table doesn't have a unique (surrogate) key,
          -- you can use the internal "ctid" which is guaranteed to be unique
          -- AND x.ctid < mt.ctid
        );

对于您的最终删除查询:只需将 SELECT COUNT(*) 替换为 DELETE。