SQL Server 中的 Case 语句用于识别可能的重复项

我有一个包含一些记录的临时表。

CREATE TABLE #TEMP
(
PER_ID INT,
PER_SS VARCHAR (11),
PER_MISC VARCHAR (25),
PER_CLASS VARCHAR (20),
FILE_DT DATETIME,
PER_CASE_ID INT,
PER_CODE VARCHAR (5),
CREATE_DT DATETIME,
END_DT DATETIME

)

INSERT INTO #TEMP VALUES('9801745','6385789','21 ADC 846/841(T)(1)(B)','NMT785989','11/11/2016 12:00 AM','957800','IDA','11/12/2016 11:00 PM','11/17/2016 12:00 AM')
INSERT INTO #TEMP VALUES('9801745','6385789','21 B 1325(Q)(4)','NMT785989','11/11/2016 12:00 AM','957800','CAN','11/12/2016 11:00 PM','11/21/2016 12:00 AM')
INSERT INTO #TEMP VALUES('9801745','6385789','21 B 1325(Q)(4)','NMT785989','11/11/2016 12:00 AM','698080','QBC','11/12/2016 11:00 PM','11/21/2016 12:00 AM')
INSERT INTO #TEMP VALUES('9801745','6385789','88 EEA 1326 (K)(1)','NMT785989','11/11/2016 12:00 AM','957800','CAN','11/12/2016 12:15 PM','')
INSERT INTO #TEMP VALUES('9801745','6385789','88 EEA 1326 (K)(1)','NMT785989','11/11/2016 12:00 AM','698080','DLC','11/12/2016 12:15 PM','')

我尝试使用 SQL Server 中的 case 语句通过创建几个额外的字段（例如下面的结果）来识别可能的重复记录：

same per id same per ss same per misc   same per class  same file date  per id  per ss  per misc    per  class  file date   per case id per code    create date end date
yes yes no  yes yes 9801745 6385789 21 ADC 846/841(T)(1)(B) NMT785989   11/11/2016  957800  IDA 11/12/2016  11/17/2016
yes yes yes yes yes 9801745 6385789 21 B 1325(Q)(4) NMT785989   11/11/2016  957800  CAN 11/12/2016  11/21/2016
yes yes yes yes yes 9801745 6385789 21 B 1325(Q)(4) NMT785989   11/11/2016  698080  QBC 11/12/2016  11/21/2016
yes yes yes yes yes 9801745 6385789 88 EEA 1326 (K)(1)  NMT785989   11/11/2016  957800  CAN 11/12/2016  
yes yes yes yes yes 9801745 6385789 88 EEA 1326 (K)(1)  NMT785989   11/11/2016  698080  DLC 11/12/2016  

基本上，如果每个 id 列相同，则为每个 id 列指定“是”，如果每个 ss 列相同，则为每个 ss 指定“是”，依此类推。我尝试了我的 SQL Server 代码如下：

SELECT DISTINCT
CASE 
WHEN A.PER_ID = B.PER_ID THEN 'YES' ELSE 'NO' END AS SAME_PER_ID,

CASE 
WHEN A.PER_SS = B.PER_SS THEN 'YES' ELSE 'NO' END AS SAME_PER_SS,

CASE 
WHEN A.PER_MISC = B.PER_MISC THEN 'YES' ELSE 'NO' END AS SAME_PER_MISC,

CASE 
WHEN A.PER_CLASS = B.PER_CLASS THEN 'YES' ELSE 'NO' END AS SAME_PER_CLASS,

CASE 
WHEN A.FILE_DT = B.FILE_DT THEN 'YES' ELSE 'NO' END AS SAME_FILE_DT,
A.PER_ID, 
A.PER_SS, 
A.PER_MISC,
A.PER_CASE_ID,
A.PER_CLASS,
A.FILE_DT,
A.CREATE_DT,
A.END_DT
FROM #TEMP A INNER JOIN #TEMP B
ON A.PER_ID = B.PER_ID
ORDER BY A.PER_ID, A.PER_CASE_ID

但是，我返回了 10 条记录，而不是仅 5 条记录，结果不是我所期望的。 SQL Server 有没有办法可以解决这个问题？

我猜您想显示一组列的不同行中的 per_id 是否有不同的值。您可以使用窗口函数MIN and MAX检查同一 per_id 的不同列中是否有多个不同的值。

select 
    t.*,
    case when max(PER_SS) over (partition by per_id) = min(PER_SS) over (partition by per_id) then 'Yes' else 'No' end as same_per_ss,
    case when max(PER_MISC) over (partition by per_id) = min(PER_MISC) over (partition by per_id) then 'Yes' else 'No' end as same_per_misc,
    case when max(PER_CLASS) over (partition by per_id) = min(PER_CLASS) over (partition by per_id) then 'Yes' else 'No' end as same_per_class,
    case when max(FILE_DT) over (partition by per_id) = min(FILE_DT) over (partition by per_id) then 'Yes' else 'No' end as same_per_file_dt
from #temp t;