SQL Server标识所有重复行

Question

我在一个表格里有这样的信息：

id  Value   Date        Amount
------------------------------
1   79111   2016-10-10  700.00
2   79111   2016-10-10  800.00
3   79111   2016-10-12  900.00
4   79111   2016-10-10  500.00
5   79111   2016-10-27  100.00

我想标识所有具有相同值和Date信息的行，如下所示：

id  Value   Date        Amount  Alert
----------------------------------------
1   79111   2016-10-10  700.00  duplicate
2   79111   2016-10-10  800.00  duplicate
3   79111   2016-10-12  900.00  NULL
4   79111   2016-10-10  500.00  duplicate
5   79111   2016-10-27  100.00  NULL

我已经识别了"X“行的重复行.但我也需要将"X“行识别为重复记录。我的问题是：

WITH CTE AS
(
SELECT *,RN=ROW_NUMBER() OVER (PARTITION BY Value_my, Date_my ORDER BY id DESC) FROM myTable
)
select *
from CTE A
where A.RN>1 Or A.RN = 1

但我有一个：

id  Value   Date        Amount  Alert
----------------------------------------
1   79111   2016-10-10  700.00  NULL ***How identify this row?***
2   79111   2016-10-10  800.00  duplicate
3   79111   2016-10-12  900.00  NULL
4   79111   2016-10-10  500.00  duplicate
5   79111   2016-10-27  100.00  NULL

有什么想法吗？

Answer 1

这不仅可以识别重复的记录，还可以识别哪些记录是被欺骗的。

Declare @Yourtable table (ID int,value int,Date date,Amount money) 
Insert into @Yourtable values 
(1,79111,'2016-10-10',700.00),
(2,79111,'2016-10-10',800.00),
(3,79111,'2016-10-12',900.00),
(4,79111,'2016-10-10',500.00),
(5,79111,'2016-10-27',100.00)

Select A.*
      ,Alert = case when B.DuplicateOf is null then Null else 'Alert' end
      ,B.DuplicateOf 
 From  @YourTable A
 Cross Apply (Select DuplicateOf = Stuff((Select Distinct ',' + cast(id as varchar(25)) 
                              From @YourTable 
                              Where id<>A.id
                                and value=A.value
                                and date =A.date
                                For XML Path ('')),1,1,'')
              ) B
 --Where Dupes is not null

返回

ID  value   Date        Amount  Alert   DuplicateOf
1   79111   2016-10-10  700.00  Alert   2,4
2   79111   2016-10-10  800.00  Alert   1,4
3   79111   2016-10-12  900.00  NULL    NULL
4   79111   2016-10-10  500.00  Alert   1,2
5   79111   2016-10-27  100.00  NULL    NULL

Answer 2

多亏了@john-cappelletti，我找到了一个基于john的答案的解决方案：

select  a.*,
        case when b.DuplicateOf is null then 0 else 1 end 'Duplicity'
from    myTable a
outer apply (
    Select top 1 id 'DuplicateOf'
    from myTable
    where id <> a.id
    and Value_my = a.Value_my
    and Date_my = a.Date_my
)b