SQL Server筛选出重复项等

Question

我需要SQL Server 2014(v12.0(过滤重复项的帮助，这可能是一个太大的问题，但如果有任何帮助，我们将不胜感激。在导入一个数据表后，我们有一些重复的数据要管理。

数据示例如下：

人员1

eCRFNo
101	aaa.com
101	aaa.com	第2个人
101	aaa.com	第3个人
101	bbb.com	第4个人

由于您使用的是MS SQL v12.0(SQL Server 2014(，因此您需要使用SQL Server的XML功能(而不是在SQL Server>=2017中使用STRING_AGG(。

使用CTE获取DENSE_RANK和ROW_NUMBER，然后将行转换为XML字符串，然后使用SQL Server的XML功能。

WITH CTE AS (SELECT *,
DENSE_RANK() OVER (PARTITION BY ecRFNo ORDER BY ecRFNo, NotesURL ASC) as DR,
ROW_NUMBER() OVER (PARTITION BY ecRFNo ORDER BY ecRFNo, NotesURL, Assignee ASC) as RN
FROM your_table)
SELECT *,
CASE WHEN a.Watcher IS NOT NULL THEN 'Replication' ELSE 'Unique' END AS Label
FROM (SELECT ecRFNo, NotesURL, Assignee,
stuff( (select DISTINCT ', ' + cast(t.Assignee as varchar(max))
from CTE t
WHERE RN > 1 AND t.ecRFNo = a.ecRFNo AND t.NotesURL = a.NotesURL
for xml path ('')
), 1, 2, '') AS Watcher
FROM CTE a WHERE DR = 1 AND RN = 1
GROUP BY ecRFNo, NotesURL, Assignee) a

注意：如果未找到重复项，则列Watcher将显示NULL值，列Label将显示值Unique。

如果要UPDATE表和DELETE不再需要的行，请添加新列，然后用UDPATE语句将上面的查询包装到另一个CTE中：

ALTER TABLE your_table
ADD 
Watcher VARCHAR(250),
Label VARCHAR(25);

WITH CTE AS (SELECT *,
DENSE_RANK() OVER (PARTITION BY ecRFNo ORDER BY ecRFNo, NotesURL ASC) as DR,
ROW_NUMBER() OVER (PARTITION BY ecRFNo ORDER BY ecRFNo, NotesURL, Assignee ASC) as RN
FROM your_table),
CTE2 AS 
(SELECT *,
CASE WHEN a.Watcher IS NOT NULL THEN 'Replication' ELSE 'Unique' END AS Label
FROM (SELECT ecRFNo, NotesURL, Assignee,
stuff( (select DISTINCT ', ' + cast(t.Assignee as varchar(max))
from CTE t
WHERE RN > 1 AND t.ecRFNo = a.ecRFNo AND t.NotesURL = a.NotesURL
for xml path ('')
), 1, 2, '') AS Watcher
FROM CTE a WHERE DR = 1 AND RN = 1
GROUP BY ecRFNo, NotesURL, Assignee) a)
UPDATE your_table
SET your_table.Watcher = b.Watcher, your_table.Label = b.Label
FROM CTE2 b
WHERE your_table.ecRFNo = b.ecRFNo 
AND your_table.NotesURL = b.NotesURL 
AND your_table.Assignee = b.Assignee

最后，通过使用Label列来DELETE不再需要的行；未被上述CCD_ 16触摸的任何东西将保持为CCD_。

DELETE FROM your_table WHERE Label IS NULL

结果：

| ecRFNO | NotesURL | Assignee | Watcher            | Label       |
|--------|----------|----------|--------------------|-------------|
| 101    | aaa.com  | Person 1 | Person 2, Person 3 | Replication |

在这里打转