sql - 使用 PostgreSQL 按 3 列检查数据的唯一性

Question

我在使用 PostgreSQL 检查数据的唯一性时遇到问题。我有包含以下数据的人员表:

 id | identifier | first_name | middle_name | last_name |      email      |         created_at         |         updated_at         
----+------------+------------+-------------+-----------+-----------------+----------------------------+----------------------------
  1 | identifier | First      | A.          | Last      | some@email.com  | 2015-11-11 14:46:17.782689 | 2015-11-11 14:46:17.782689
  2 | identifier | First 2    | M.          | Last 2    | some@email2.com | 2015-11-11 14:46:17.790697 | 2015-11-11 14:46:17.790697
(2 rows)

现在我想查找属于多个名字 - 姓氏组合的记录的 ID。因此，在此示例中，我们有两条具有相同标识符但名字和姓氏不同的记录。我试图检查以下 sql 是否有任何重复项，但这不会返回任何内容:

SELECT
    identifier, first_name, last_name, COUNT(*)
FROM
    people
GROUP BY
    identifier, first_name, last_name
HAVING 
    COUNT(*) > 1

Answer 1

如果您只想要重复的标识符:

select identifer
from people p
group by identifer
having count(*) > 1;

如果您想要名称不同的标识符:

select identifer
from people p
group by identifer
having min(first_name) <> max(first_name) or
       min(last_name) <> max(last_name);

(或者:有 count(distinct first_name, last_name) > 1。)

如果你想要原始行，我会使用窗口函数:

select p.*
from (select p.*,
             min(first_name) over (partition by identifer) as minfn,
             max(first_name) over (partition by identifer) as maxfn,
             min(last_name) over (partition by identifer) as minln,
             min(last_name) over (partition by identifer) as maxln,
      from people
     ) p
where minfn <> maxfn or minln <> maxln;

如果 Postgres 支持 count(distinct) 作为窗口函数，这会更容易。