我一直在阅读以下关于使用 CTE 进行递归查询的 Microsoft article ,但似乎无法理解如何将它用于组公共(public)项目。
我有一张表,其中包含以下列:
我需要做的是从表中的第一个人开始并遍历表并找到所有具有相同(
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
数据脚本:
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 是唯一的。