sql-server - 将相同的组 ID 分配给具有相同值的 ID - 递归 sql 查询

Question

该表包含 PID 和相应的值。具有相同值的 PID 应分配相同的组 ID。A、B、D 直接相连（值 = ABC），并且由于 C 与 D 相连（值 = GHI），因此 A、B、C 和 D 都属于同一组。

PID    Value 
--------------
A101    ABC    
A101    XYZ       
B101    ABC     
C101    GHI     
C101    DEF    
D101    ABC    
D101    GHI    
E101    KLM    
F101    MNO    

预期结果：

PID    GroupID
--------------
A101     1       
B101     1       
C101     1     
D101     1     
E101     2  
F101     3

我尝试了以下查询但没有成功：

with cte as (     
select PID, Value, Rank() OVER(ORDER BY Value) rn          
from  t           
union all         
select t.PID, cte.Value, cte.rn       
from t join cte       
     on t.Value = cte.Value and              
        t.PID < cte.PID  
)           
select *      
from cte

有没有办法修复查询并在 SQL Server 2012/2014 中编写更高效的查询（约 100 万行）？

标签： sql-serverrecursionparent-childsocial-networkingsna

Answer 1

尝试以下操作：

declare @tab table (PID varchar(10), Val varchar(100))
insert into @tab select 'A101', 'ABC'
insert into @tab select 'A101', 'XYZ'
insert into @tab select 'B101', 'ABC'
insert into @tab select 'C101', 'GHI'
insert into @tab select 'C101', 'DEF'
insert into @tab select 'D101', 'ABC'
insert into @tab select 'D101', 'GHI'
insert into @tab select 'E101', 'KLM'
insert into @tab select 'F101', 'MNO'

select PID, dense_rank() over (order by group_id) GROUP_ID
from
(
select x.PID, min(t.num) group_id from @tab X
join (select val, dense_rank() over (order by val) num from @tab)t on t.Val = X.Val
group by x.PID
)t2
order by 2