如何编写SQL查询基于多列识别数据表中的重复行？

假设我们有如下Users表结构：

CREATE TABLE Users 
(
    ID int,
    NAME varchar(255),
    ADDRESS varchar(255)
);

表中现有数据：

想要找出NAME和ADDRESS列值完全相同的重复行，以下是两种可行的查询方案：

方案一：使用窗口函数（推荐） #

利用窗口函数直接标记重复组内的所有行，写法简洁高效：

SELECT ID, NAME, ADDRESS
FROM (
    SELECT 
        ID, NAME, ADDRESS,
        -- 按NAME和ADDRESS分组，统计每组的行数
        COUNT(*) OVER (PARTITION BY NAME, ADDRESS) AS duplicate_count
    FROM Users
) AS sub_query
-- 筛选出组内行数大于1的所有行
WHERE duplicate_count > 1;

方案二：使用GROUP BY + 关联查询 #

先通过分组找出重复的列组合，再关联原表获取完整行数据：

SELECT u.ID, u.NAME, u.ADDRESS
FROM Users u
INNER JOIN (
    -- 找出存在重复的NAME和ADDRESS组合
    SELECT NAME, ADDRESS
    FROM Users
    GROUP BY NAME, ADDRESS
    HAVING COUNT(*) > 1
) AS duplicate_groups 
    ON u.NAME = duplicate_groups.NAME 
    AND u.ADDRESS = duplicate_groups.ADDRESS;

以上两种查询都会返回期望的结果：

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