时间戳

首页 > 解决方案 > SQL：递归循环

sql - SQL：递归循环

问题描述

表如下：

交换

我试图让结果集包含与表相交的所有人的组，因此在附加表的结果集中创建以下组。

Person1, Person2, Person3, Person7, Person8
Person5, Person6, Person9

到目前为止，我有以下查询，但似乎无法将结果与行表相交并输出为 1 列。

DECLARE @r VARCHAR(MAX), @n INT, @i INT 
SELECT @i = 1,
       @r = 'SELECT BOX, ' + CHAR(13), 
       @n = (SELECT TOP 1 COUNT( USERS ) 
                   FROM EXCHANGE 
                  GROUP BY BOX 
                  ORDER BY COUNT( USERS ) DESC ) ;      
WHILE @i <= @n BEGIN 
           SET @r = @r + 
           CASE WHEN @i = 1  
                THEN 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + ' 
                                 THEN USERS 
                                            ELSE SPACE(0) END ) + ' + CHAR(13) 
           WHEN @i = @n 
             THEN 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + ' 
                                 THEN '', '' + USERS 
                                 ELSE SPACE(0) END ) ' + CHAR(13) 
             ELSE 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + ' 
                                 THEN '', '' + USERS 
                                 ELSE SPACE(0) END ) + ' + CHAR(13)  
           END ;
           SET @i = @i + 1 ;
END 
SET @r = @r + ' 
    FROM ( SELECT BOX, USERS, 
                  ROW_NUMBER() OVER ( PARTITION BY BOX ORDER BY USERS )
             FROM EXCHANGE p ) D ( BOX, USERS, Seq ) 
           GROUP BY BOX;' 
EXEC( @r ) ;

标签： sqlsql-servergroup-bycommon-table-expression

解决方案

这种类型的图遍历在 SQL Server 中是一种痛苦——你有循环。问题是避免循环。因为 SQL Server 没有很好的数据类型，所以需要将访问的节点存储为字符串。

您可以在递归 CTE 中完成所有这些操作。这个想法是遵循节点的所有路径而不重复任何节点。保持最小节点被访问。瞧！指定路径：

with cte as (
      select box, users,
             convert(varchar(max), concat(',', box, ',', users, ',')) as path,
             (case when box < users then box else users end) as min_node
      from exchange
      union all
      select cte.box, e.users,
             concat(cte.path, e.users, ','),
             (case when min_node < e.users then min_node else e.users end)
      from cte join
           exchange e
           on e.box = cte.users
      where path not like '%,' + e.users + ',%'
     )
select cte.box, min(cte.users), min(cte.path), min(cte.min_node) as grouping
from cte
group by cte.box;

这是一个 db<>fiddle。

这假设边是对称的，所以如果你有 (a, b)，你也有 (b, a)。

如果不是这种情况，很容易添加一个 CTE 来实现这种情况：

select box, users
from exchange
union   -- on purpose to remove duplicates
select users, box
from exchange;

推荐阅读