如何使用SQL从directors表中获取基于name和phone_number的重复行

你的原SQL逻辑有误：uuid是表的唯一标识，按uuid分组后每组仅包含一行数据，count(d2.phone_number)和count(d2.name)永远等于1，根本无法筛选出重复行。

以下是几种正确的实现方式：

方法1：窗口函数（推荐，支持MySQL 8+、PostgreSQL、SQL Server等） #

通过窗口函数计算每个name和phone_number的出现次数，直接筛选出次数大于1的行：

SELECT uuid, name, phone_number
FROM (
    SELECT 
        uuid, 
        name, 
        phone_number,
        COUNT(*) OVER (PARTITION BY name) AS name_count,
        COUNT(*) OVER (PARTITION BY phone_number) AS phone_count
    FROM directors
) t
WHERE name_count > 1 OR phone_count > 1;

方法2：EXISTS子查询 #

判断当前行的name或phone_number在其他行存在重复：

SELECT d1.*
FROM directors d1
WHERE EXISTS (
    SELECT 1 
    FROM directors d2 
    WHERE d2.uuid != d1.uuid 
      AND (d2.name = d1.name OR d2.phone_number = d1.phone_number)
);

方法3：先收集重复值再关联 #

先找出所有重复的name和phone_number，再关联原表获取对应行：

SELECT d.*
FROM directors d
JOIN (
    -- 提取重复的name
    SELECT name FROM directors GROUP BY name HAVING COUNT(*) > 1
    UNION
    -- 提取重复的phone_number
    SELECT phone_number FROM directors GROUP BY phone_number HAVING COUNT(*) > 1
) dup ON d.name = dup.name OR d.phone_number = dup.phone_number;

以上三种方法都能正确返回你需要的结果：包含phone_number重复的前两行，以及name重复的第一行和最后一行。

内容的提问来源于stack exchange，提问作者issdilshod