基于匹配列数的SQL去重

Question

我正在尝试查找有关如何根据记录之间的匹配列数对表进行重复数据删除的信息。

假设我的数据源看起来像

---------------------------------------------------
| ColumnA | ColumnB | ColumnC | ColumnD | ColumnN |
---------------------------------------------------
| Peter   | Dink    | Midget  | NULL    | 0738455 |
| Peter   | Dink    | Child   | 334AA   | 49595   |
| Mark    | Walhg   | Funky   | 334AA   | 0738455 | 
| Mark    | Dink    | NULL    | NULL    | NULL    |
| Mark    | Walhg   | Funky   | 334AA   | NULL    |
| Peter   | Dink    | NULL    | NULL    | 0738455 |
---------------------------------------------------

基本上，我希望能够提供共享 2、3、4 等数据列的记录数；但是我需要将其限制为仅选定的列子集(并忽略 NULL/空白)。

根据上面的数据，我想能够说:

1. 有 0 条记录在 5 列上匹配
2. 有 1 条记录在 4 列 (3,5) 上匹配
3. 有 1 条记录匹配 3 列 (1,6) (3,5)
4. 有 2 条记录在 2 列 (1,6) (2,6) (3,5) (1,2) 上匹配

随着匹配列的数量越来越少，我还需要它像“向下移动”一样。所以在上面我的数据在检查 5 列的匹配后是相同的。然后在 4 列上，数据减少为:

---------------------------------------------------
| ColumnA | ColumnB | ColumnC | ColumnD | ColumnN |
---------------------------------------------------
| Peter   | Dink    | Midget  | NULL    | 0738455 |
| Peter   | Dink    | Child   | 334AA   | 49595   |
| Mark    | Walhg   | Funky   | 334AA   | 0738455 | 
| Mark    | Dink    | NULL    | NULL    | NULL    |
| Peter   | Dink    | NULL    | NULL    | 0738455 |
---------------------------------------------------

第 5 列消失了，因为它已被删除(我不知道我是如何决定删除哪一个的，可能是在某个日期列上)。所以我可以说删除了 1 条记录。

检查 3 列后:

---------------------------------------------------
| ColumnA | ColumnB | ColumnC | ColumnD | ColumnN |
---------------------------------------------------
| Peter   | Dink    | Midget  | NULL    | 0738455 |
| Peter   | Dink    | Child   | 334AA   | 49595   |
| Mark    | Walhg   | Funky   | 334AA   | 0738455 | 
| Mark    | Dink    | NULL    | NULL    | NULL    |
---------------------------------------------------

所以我可以说另一个 1 被删除了。

然后是 2 列:

---------------------------------------------------
| ColumnA | ColumnB | ColumnC | ColumnD | ColumnN |
---------------------------------------------------
| Peter   | Dink    | Midget  | NULL    | 0738455 |
| Mark    | Walhg   | Funky   | 334AA   | 0738455 | 
| Mark    | Dink    | NULL    | NULL    | NULL    |
---------------------------------------------------

删除了另一列。

我认为我会采用的方法是给出一个权重，该权重本质上是来自所选列的匹配数据点的数量。例如，我可能不想使用“国家/地区”列作为匹配列之一，我只会使用可标识记录的内容，例如姓名和电话号码。

然后我可以查看在每个权重(列匹配数)下删除了多少记录，并决定我们将删除所有具有 7 个匹配列的身份数据；并汇总一条记录中的任何值，这些值在其重复记录中为 NULL/空白。

这一切都超出了我的范围。我知道我想做什么；只是不知道该怎么做。

Answer 1

希望我理解正确。这是我关于如何做到这一点的想法，它还不完整，您可以使用动态 sql 和 while 循环更多地自动化它以遍历所有 ID 并稍后统一结果。

IF OBJECT_ID('TestTable1') IS NOT NULL 
DROP TABLE TestTable1

CREATE TABLE TestTable1 (
    ID INT IDENTITY(1,1),
    ColumnA NVARCHAR(100),
    ColumnB NVARCHAR(100),
    ColumnC NVARCHAR(100),
    ColumnD NVARCHAR(100),
    ColumnE INT
)

INSERT INTO TestTable1 VALUES 
('Peter','Dink','Milk',NULL,0738455),
('Peter','Dink','Beer','334AA',49595),
('Mark','Walk','Funky','334AA',0738455),
('Mark','Dink',NULL,NULL,NULL),
('Mark','Walk','Funky','334AA',NULL),
('Peter','Dink',NULL,NULL,0738455)

DECLARE @ID INT
SET @ID = 1

SELECT * FROM TestTable1 WHERE ID IN 
(
    SELECT ID FROM
    (   
        SELECT @ID AS ID
        UNION
        SELECT b.ID FROM TestTable1 as a
        CROSS APPLY TestTable1 as b
        WHERE a.ColumnA = b.ColumnA
        AND a.ID = @ID AND b.ID <> @ID
    ) AS OneMatchingColumn
) 


SELECT * FROM TestTable1 WHERE ID IN 
(
    SELECT ID FROM
    (
        SELECT @ID AS ID
        UNION
        SELECT b.ID FROM TestTable1 as a
        CROSS APPLY TestTable1 as b
        WHERE a.ColumnA = b.ColumnA
        AND a.ColumnB = b.ColumnB
        AND a.ID = @ID AND b.ID <> @ID
    ) AS TwoMatchingColumns
)


SELECT * FROM TestTable1 WHERE ID IN 
(
    SELECT ID FROM
    (
        SELECT @ID AS ID
        UNION
        SELECT b.ID FROM TestTable1 as a
        CROSS APPLY TestTable1 as b
        WHERE a.ColumnA = b.ColumnA
        AND a.ColumnB = b.ColumnB
        AND a.ColumnC = b.ColumnC
        AND a.ID = @ID AND b.ID <> @ID
    ) AS ThreeMatchingColumns
)