SQL Server中如何查询personId和Date重复的完整行记录

你可以通过以下几种方法实现需求，获取包含location的完整重复记录：

方法1：窗口函数（推荐，现代SQL数据库适用） #

利用COUNT() OVER()窗口函数直接计算每组的记录数，再筛选出组内记录数大于1的行：

SELECT personId, Date, location
FROM (
    SELECT 
        personId, 
        Date, 
        location,
        COUNT(*) OVER (PARTITION BY personId, Date) AS record_count
    FROM sampleTable
) grouped_data
WHERE record_count > 1

逻辑说明：PARTITION BY personId, Date会将数据按用户ID和日期分组，COUNT(*) OVER()计算每个分组的总记录数，外层查询筛选出总记录数大于1的所有行，即可得到完整的重复记录。

方法2：子查询关联（兼容老版本数据库） #

先通过分组查询找出存在重复的personId+Date组合，再关联原表获取完整记录：

SELECT st.*
FROM sampleTable st
INNER JOIN (
    SELECT personId, Date 
    FROM sampleTable 
    GROUP BY personId, Date 
    HAVING COUNT(*) > 1
) duplicate_pairs ON st.personId = duplicate_pairs.personId 
                  AND st.Date = duplicate_pairs.Date

逻辑说明：子查询先筛选出有多个location的用户日期组合，再通过INNER JOIN把原表中符合这些组合的所有行都拉出来。

方法3：EXISTS子查询 #

通过检查每条记录是否存在同用户同日期但不同地点的其他记录，来筛选目标行：

SELECT st.*
FROM sampleTable st
WHERE EXISTS (
    SELECT 1
    FROM sampleTable st2
    WHERE st2.personId = st.personId 
      AND st2.Date = st.Date 
      AND st2.location != st.location
)

逻辑说明：对于原表的每一行，判断是否存在同一用户同一日期下的其他地点记录，存在则保留当前行。

方法选择建议 #

• 窗口函数写法最简洁，性能最优，适合MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库。
• 子查询关联兼容性最好，能在不支持窗口函数的老版本数据库中运行。
• EXISTS写法逻辑直观，但大数据量下性能可能不如前两种。

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