SQL查询两列数据异常：提取同一名称对应多ID的行

问题描述 #

现有包含ID、NAME两列的数据库表，数据如下：

ID NAME
1  ALPHA
1  ALPHA
2  BETA
1  BETA

其中前3行数据正确，最后一行存在异常（ID误输入为1，应为2）。需要编写SQL查询提取同一名称对应多个不同ID的行，此前尝试的查询无法得到正确结果：

SELECT F1.ID FROM myTable F1 WHERE F1.Name in (SELECT DISTINCT F2.Name FROM myTable F2)

问题分析 #

你之前的查询逻辑存在漏洞：子查询仅返回所有不重复的NAME，相当于没有做任何有效筛选——表中所有行的NAME都属于这个集合，最终会返回所有ID，无法定位到关联多ID的名称对应的行。

正确查询方案 #

方案1：分组筛选关联法（兼容多数数据库） #

先通过子查询找出存在多个不同ID的NAME，再关联原表获取对应行：

SELECT t.ID, t.NAME
FROM myTable t
INNER JOIN (
    -- 筛选出对应多个不同ID的名称
    SELECT NAME
    FROM myTable
    GROUP BY NAME
    HAVING COUNT(DISTINCT ID) > 1
) AS problematic_names ON t.NAME = problematic_names.NAME;

方案2：窗口函数法（适用于支持窗口函数的数据库：MySQL 8+、PostgreSQL、SQL Server等） #

利用窗口函数按NAME分组统计不同ID的数量，再筛选符合条件的行：

SELECT ID, NAME
FROM (
    SELECT 
        ID, 
        NAME,
        -- 按NAME分组统计当前名称对应的不同ID总数
        COUNT(DISTINCT ID) OVER (PARTITION BY NAME) AS id_count
    FROM myTable
) AS sub
WHERE id_count > 1;

执行结果 #

以上两种查询都会返回以下行（即BETA对应的两行，该名称关联了ID1和ID2两个不同值）：

ID NAME
2  BETA
1  BETA

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