SQL递归CTE:查找按属性链接的对象 [英] SQL Recursive CTE: Finding objects linked by property

问题描述

我只是想了解CTE和递归，以解决以前使用游标的问题.

I'm just trying to understand CTE and recursion to solve an issue that I would previously have used a cursor for.

create table ##ACC (
AccNo int,
Property char
)

Insert into ##ACC 
VALUES (1,'A'),(1,'B'),(2,'A'),(2,'C'),(3,'C'),(4,'D')

我要实现的目的是通过属性获取所有AccNo以及与它们相关的所有AccNo的列表.所以我的预期结果是

What I'm trying to achieve is to get a list of all AccNo's, and all AccNo's they're related to via Property. So my expected results are

PrimaryAccNo | LinkedAccNo
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2
3 | 3
4 | 4

我尝试了以下代码和变体，但我要么仅获得4个结果(PrimaryAccNo = LinkedAccNo)，要么我遇到了100个递归.

I've attempted the following code and variations but I either get 4 results (PrimaryAccNo=LinkedAccNo) only or I hit 100 recursions.

WITH Groups(PrimaryAccNo, LinkedAccNo)
AS
(
Select distinct AccNo, AccNo from ##ACC

UNION ALL
Select g.PrimaryAccNo, p.AccNo from
##ACC p inner join Groups g on p.AccNo=g.LinkedAccNo
inner join ##ACC pp on p.Property=pp.Property
where p.AccNo<> pp.AccNo
)
Select PrimaryAccNo,LinkedAccNo 
from Groups

我在做什么错了?

推荐答案

由于数据中的周期，您正在陷入无限循环，例如:1> 2> 3> 2> ....解决方案是跟踪已经消耗"的行.由于CTE的限制，这必须通过在每个CTE行中包括历史记录来完成，例如通过组装到达每一行的路径.您可以在最后一个select上取消, Path的注释，以查看发生了什么.

You're running into an infinite loop caused by cycles within your data, e.g.: 1 > 2 > 3 > 2 > ... . The solution is to keep track of the rows that have already been "consumed". Due to limitations in CTEs, this has to be done by including the history within each CTE row, e.g. by assembling the path followed to arrive at each row. You can uncomment the , Path on the final select to see what is going on.

-- Sample data.
declare @ACC as Table ( AccNo Int, Property Char );
insert into @ACC values
  ( 1, 'A' ), ( 1, 'B' ), ( 2, 'A' ), ( 2, 'C' ), ( 3, 'C' ), ( 4, 'D' );
select * from @ACC;

-- Recursive CTE.
with Groups as (
  select distinct AccNo, AccNo as LinkedAccNo,
    Cast( '|' + Cast( AccNo as VarChar(10) ) + '|' as VarChar(1024) ) as Path
    from @ACC
  union all
  select G.AccNo, A.AccNo, Cast( Path + Cast( A.AccNo as VarChar(10) ) + '|' as VarChar(1024) )
    from Groups as G inner join -- Take the latest round of new rows ...
      @ACC as AP on AP.AccNo = G.LinkedAccNo inner join -- ... and get the   Property   for each ...
      @ACC as A on A.Property = AP.Property -- ... to find new linked rows.
      where G.Path not like '%|' + Cast( A.AccNo as VarChar(10) ) + '|%' )
  select AccNo, LinkedAccNo -- , Path
    from Groups
    order by AccNo, LinkedAccNo;

这篇关于SQL递归CTE:查找按属性链接的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！