如何查询LAST_NAME、FIRST_NAME相同但EMAIL不同的完整记录

以下几种SQL语句都能满足需求，输出所有LAST_NAME与FIRST_NAME相同但EMAIL不同的完整记录：

方法一：窗口函数（推荐） #

利用窗口函数按姓名分组统计，直接筛选符合条件的记录：

SELECT *
FROM (
    SELECT 
        *,
        -- 统计同姓名的总记录数
        COUNT(*) OVER(PARTITION BY LAST_NAME, FIRST_NAME) AS name_record_count,
        -- 统计同姓名下不同邮箱的数量
        COUNT(DISTINCT EMAIL) OVER(PARTITION BY LAST_NAME, FIRST_NAME) AS distinct_email_count
    FROM your_table_name
) filtered
WHERE name_record_count >= 2 AND distinct_email_count >= 2;

方法二：自连接 #

通过自连接匹配同姓名但不同邮箱的记录，用DISTINCT避免重复输出：

SELECT DISTINCT t1.*
FROM your_table_name t1
INNER JOIN your_table_name t2
    ON t1.LAST_NAME = t2.LAST_NAME
    AND t1.FIRST_NAME = t2.FIRST_NAME
    AND t1.EMAIL != t2.EMAIL;

方法三：子查询筛选姓名组 #

先找出存在多邮箱的姓名组合，再关联原表取出对应记录：

SELECT t.*
FROM your_table_name t
INNER JOIN (
    SELECT LAST_NAME, FIRST_NAME
    FROM your_table_name
    GROUP BY LAST_NAME, FIRST_NAME
    HAVING COUNT(DISTINCT EMAIL) > 1
) valid_name_groups
    ON t.LAST_NAME = valid_name_groups.LAST_NAME
    AND t.FIRST_NAME = valid_name_groups.FIRST_NAME;

注意：将上述语句中的your_table_name替换为你的实际表名。

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