时间戳

首页 > 解决方案 > 如何编写 case 语句以返回重复记录？

sql - 如何编写 case 语句以返回重复记录？

问题描述

我有一个姓名和地址表，并希望返回完整的姓名和地址列表，并带有一个额外的列，用于标识地址是重复地址还是唯一地址。当我运行一个更简单的语句时，我可以看到几个重复的地址条目——

SELECT PersonAddressLine1, Count (*)
FROM CompanyTable1
GROUP BY PersonAddressLine1

或者

SELECT PersonAddressLine1, Count (*)
FROM CompanyTable1
GROUP BY PersonAddressLine1
HAVING COUNT (*) > 1

但我需要返回带有列的完整表 -

PersonID, PersonName, PersonAddressLine1, AddressVerification (Duplicate / Unique), CompanyID, CompanyName.

PersonID - 1
PersonID - 2
PersonID - 3
PersonName - Ryan
PersonName - Andrew
PersonName - Ben
PersonAddressLine1 - 100 Avenue
PersonAddressLine1 - 100 Avenue
PersonAddressLine1 - 200 Avenue

如果我使用下面的 case 语句，组条件会影响 PersonAddressLine1 计数结果。它显示所有地址都是唯一的。

SELECT PersonID, PersonName, PersonAddressLine1, CompanyID, CompanyName, 

CASE WHEN count(*) > 1 
THEN CONCAT(PersonName,' ','Address Line 1 is duplicate')
    ELSE CONCAT(PersonName,' ','Address Line 1 is unique')
END AS 'Person Address Verification'

FROM CompanyTable1

GROUP by PersonAddressLine1, PersonID, PersonName, CompanyID, CompanyName

标签： sqlsql-server

解决方案

您可以使用窗口函数COUNT(*) OVER (PARTITION BY PersonAddressLine1)来计算重复次数

SELECT PersonID, PersonName, PersonAddressLine1, CompanyID, CompanyName, 
       CASE WHEN COUNT(*) OVER (PARTITION BY PersonAddressLine1) > 1 
            THEN CONCAT(PersonName,' ','Address Line 1 is duplicate')
            ELSE CONCAT(PersonName,' ','Address Line 1 is unique')
            END AS 'Person Address Verification'
FROM   CompanyTable1

您当前的查询现在正在做的不仅是检查，PersonAddressLine1而且是以下所有 PersonAddressLine1, PersonID, PersonName, CompanyID, CompanyName

推荐阅读