sql-server - 递归地对相似的项目进行分组

Question

我一直在阅读以下Microsoft article关于使用 CTE 的递归查询，我似乎无法理解如何将它用于组常见项目。

我有一个表，其中包含以下列:

编号

名字

姓氏

出生日期

出生国家

群组ID

我需要做的是从表格中的第一个人开始，遍历表格并找到所有相同( LastName 和 BirthCountry)或相同( DateOfBirth 和 BirthCountry)的人.

现在棘手的部分是我必须为它们分配相同的 GroupID然后对于其中的每个人 GroupID ，我需要看看其他人是否有相同的信息，然后将其放入相同的 GroupID .

我想我可以用多个游标来做到这一点，但它变得越来越棘手。

这是示例数据和输出。

ID          FirstName  LastName   DateOfBirth BirthCountry GroupID
----------- ---------- ---------- ----------- ------------ -----------
1           Jonh       Doe        1983-01-01  Grand        100
2           Jack       Stone      1976-06-08  Grand        100
3           Jane       Doe        1982-02-08  Grand        100
4           Adam       Wayne      1983-01-01  Grand        100
5           Kay        Wayne      1976-06-08  Grand        100
6           Matt       Knox       1983-01-01  Hay          101

John Doe 和 Jane Doe 属于同一组 (100)，因为它们具有相同的(姓氏和出生国)。

Adam Wayne 在第 100 组中，因为他与 John Doe 具有相同的(出生日期和出生国家/地区)。

Kay Wayne 在组 (100) 中，因为她与已经在组 (100) 中的 Adam Wayne 具有相同的(姓氏和出生国)。

Matt Knox 加入了一个新组 (101)，因为他与之前组中的任何人都不匹配。

Jack Stone 在组 (100) 中，因为他与已经在组 (100) 中的 Kay Wayne 具有相同的(出生日期和出生国家/地区)。

数据脚本:

CREATE TABLE #Tbl(
    ID              INT,
    FirstName       VARCHAR(50),
    LastName        VARCHAR(50),
    DateOfBirth     DATE,
    BirthCountry    VARCHAR(50),
    GroupID         INT NULL
);

INSERT INTO #Tbl VALUES
(1, 'Jonh', 'Doe',      '1983-01-01',   'Grand',    NULL),
(2, 'Jack', 'Stone',    '1976-06-08',   'Grand',    NULL),
(3, 'Jane', 'Doe',      '1982-02-08',   'Grand',    NULL),
(4, 'Adam', 'Wayne',    '1983-01-01',   'Grand',    NULL),
(5, 'Kay',  'Wayne',    '1976-06-08',   'Grand',    NULL),
(6, 'Matt', 'Knox',     '1983-01-01',   'Hay',      NULL);

Answer 1

这就是我想出的。我很少写递归查询，所以这对我来说是一个很好的做法。顺便说一句，Kay 和 Adam 在您的样本数据中没有共享出生国。

with data as (
    select
        LastName, DateOfBirth, BirthCountry,
        row_number() over (order by LastName, DateOfBirth, BirthCountry) as grpNum
    from T group by LastName, DateOfBirth, BirthCountry
), r as (
    select
        d.LastName, d.DateOfBirth, d.BirthCountry, d.grpNum,
        cast('|'  + cast(d.grpNum as varchar(8)) + '|' as varchar(1024)) as equ
    from data as d
    union all
    select
        d.LastName, d.DateOfBirth, d.BirthCountry, r.grpNum,
        cast(r.equ + cast(d.grpNum as varchar(8)) + '|' as varchar(1024))
    from r inner join data as d
            on      d.grpNum > r.grpNum
               and charindex('|' + cast(d.grpNum as varchar(8)) + '|', r.equ) = 0
               and (d.LastName = r.LastName or d.DateOfBirth = r.DateOfBirth)
               and  d.BirthCountry = r.BirthCountry
), g as (
    select LastName, DateOfBirth, BirthCountry, min(grpNum) as grpNum
    from r group by LastName, DateOfBirth, BirthCountry
)
select t.*, dense_rank() over (order by g.grpNum) + 100 as GroupID
from T as t 
    inner join g
        on      g.LastName = t.LastName
            and g.DateOfBirth = t.DateOfBirth
            and g.BirthCountry = t.BirthCountry

为了终止递归，有必要跟踪等价(通过字符串连接)，以便在每个级别只需要考虑新发现的等价(或连接、传递性等)。请注意，我已经避免使用单词组避免流入 GROUP BY概念。

http://rextester.com/edit/TVRVZ10193

编辑:我对等价项使用了几乎任意的编号，但是如果您希望它们出现在基于每个 block 的最低 ID 的序列中，这很容易做到。而不是使用 row_number()说 min(ID) as grpNum当然，假设 ID s 是独一无二的。