我有一个奇怪的分组场景,并找到一些麻烦找出什么是在sql中分组的最佳方式.
想象一下,我们有以下一个表
CREATE TABLE Item
(
KeyId VARCHAR(1) NOT NULL,Col1 INT NULL,Col2 INT NULL,Col3 INT NULL
)
GO
INSERT INTO Item (KeyId,Col1,Col2,Col3)
VALUES
('a',1,2,3),('b',5,4,('c',7,6),('d',8,9),('e',11,10,('f',12,13),('g',20,22,21),('h',23,24)
我需要在此表中对记录进行分组,以便如果两个记录的Col1 OR Col2或Col3相同,则这两个记录应该在同一个组中,并且应该存在链接.
换句话说,如上述数据记录’a'(第一个记录)有Col3 = 3而记录’b'(第二个记录)也有Col3 = 3,所以这两个应该在一个组中.但是记录’b’与记录’c’具有相同的Col1,因此记录’c’应该与’a’和’b’在同一组中.然后记录’d’与’c’中的Col2相同,所以这也应该在同一组中.类似地,’e’和’f’分别在Col3和Col1中具有相同的值.
另一方面,记录’g’和’h’将在一个组中(因为它们具有相同的Col2 = 22),但该组将与记录’a’,’b’,’c’的组不同,‘d’,‘E’,‘F’.
查询的结果应该是这样的
KeyId GroupId 'a' 1 'b' 1 'c' 1 'd' 1 'e' 1 'f' 1 'g' 2 'h' 2
可能有一种方法可以使用一些循环/游标,但我开始考虑更清洁的方式,这似乎很难.
解决方法
干得好：
with g (rootid,previd,level,keyid,col1,col2,col3) as (
select keyid,'-',col3 from item
union all
select g.rootid,g.keyid,g.level + 1,i.keyid,i.col1,i.col2,i.col3
from g
join item i on i.col1 = g.col1 or i.col2 = g.col2 or i.col3 = g.col3
where i.keyid > g.keyid
),m (keyid,rootid) as (
select keyid,min(rootid) from g group by keyid
)
select * from m;
结果：
keyid rootid ----- ------ a a b a c a d a e a f a g g h g
注意：请记住,sql Server在处理递归CTE时默认情况下具有100次迭代的限制(每组的行数).在英语中：尽管如上所示可以执行此操作,但sql Server可以处理的内容存在明显的限制.如果达到此限制,您将收到以下消息：
The maximum recursion 100 has been exhausted before statement completion.
如果发生这种情况,请考虑添加子句选项(maxrecursion 32767).
