我一直在阅读以下关于使用 CTE 进行递归查询的 Microsoft article ,但似乎无法理解如何将它用于组公共(public)项目。

我有一张表,其中包含以下列:

  • ID
  • 名字
  • 姓氏
  • 出生日期
  • 出生国家
  • GroupID

  • 我需要做的是从表中的第一个人开始并遍历表并找到所有具有相同( LastNameBirthCountry )或具有相同( DateOfBirthBirthCountry )的人。

    现在棘手的部分是我必须为他们分配相同的 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);
    

    最佳答案

    这是我想出的。我很少写递归查询,所以这对我来说是一个很好的做法。顺便说一下,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

    编辑:我对等价物使用了几乎任意的编号,但是如果您希望它们根据每个块的最低 ID 出现在一个序列中,这很容易做到。而不是使用 row_number()min(ID) as grpNum 假设,当然, ID s 是唯一的。

    10-07 19:47
    查看更多