SQL 递归 CTE:查找按属性链接的对象
我只是想了解 CTE 和递归,以解决我以前使用游标解决的问题.
创建表##ACC (AccNo int,属性字符)插入##ACC值 (1,'A'),(1,'B'),(2,'A'),(2,'C'),(3,'C'),(4,'D')
我想要实现的是获取所有 AccNo 的列表,以及它们通过 Property 关联的所有 AccNo.所以我的预期结果是
PrimaryAccNo |关联账号1 |11 |21 |32 |12 |22 |33 |13 |23 |34 |4
我尝试了以下代码和变体,但我要么只得到 4 个结果 (PrimaryAccNo=LinkedAccNo),要么达到 100 次递归.
WITH Groups(PrimaryAccNo, LinkedAccNo)作为(从##ACC 中选择不同的 AccNo、AccNo联合所有从中选择 g.PrimaryAccNo、p.AccNo##ACC p 内连接组 g on p.AccNo=g.LinkedAccNop.Property=pp.Property 上的内连接 ##ACC pp其中 p.AccNo 是pp.accno)选择 PrimaryAccNo,LinkedAccNo从组
我做错了什么?
解决方案另一种与您类似但不同之处在于:
- 属性值包含在递归 CTE 中,以便稍后使用
<
用于防止重复和由此产生的无限递归- 添加另一个 CTE
AccGroups
以提供关系的镜像
下面包含了一个演示小提琴:
<块引用>创建表##ACC (AccNo int,属性字符);插入##ACC值 (1,'A'),(1,'B'),(2,'A'),(2,'C'),(3,'C'),(4,'D');WITH Groups(PrimaryAccNo, LinkedAccNo, Property) AS (SELECT AccNo、AccNo、Property FROM ##ACC联合所有选择 g.PrimaryAccNo、pp.AccNo、pp.PropertyFROM 组 gINNER JOIN ##ACC p ON g.Property=p.Property ANDg.LinkedAccNo
<前>PrimaryAccNo |关联账号-----------: |----------:1 |11 |21 |32 |12 |22 |33 |13 |23 |34 |4
db<>fiddle 这里
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')
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
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
What am I doing wrong?
解决方案Another approach similar to yours but differs in the following:
- The property value is included in the recursive CTE so that it can be used later
- The
<
is used to prevent duplicates and the resulting infinite recursion - Another CTE is added
AccGroups
to provide the mirror of the relations
A demo fiddle has been included below:
CREATE TABLE ##ACC ( AccNo int, Property char ); INSERT INTO ##ACC VALUES (1,'A'),(1,'B'),(2,'A'),(2,'C'),(3,'C'),(4,'D'); WITH Groups(PrimaryAccNo, LinkedAccNo, Property) AS ( SELECT AccNo, AccNo, Property FROM ##ACC UNION ALL SELECT g.PrimaryAccNo, pp.AccNo, pp.Property FROM Groups g INNER JOIN ##ACC p ON g.Property=p.Property AND g.LinkedAccNo < p.AccNo INNER JOIN ##ACC pp ON p.AccNo = pp.AccNo ), AccGroups AS ( SELECT DISTINCT * FROM ( SELECT PrimaryAccNo, LinkedAccNo FROM Groups UNION ALL SELECT LinkedAccNo, PrimaryAccNo FROM Groups ) t ) SELECT * FROM AccGroups ORDER BY PrimaryAccNo,LinkedAccNo GO
PrimaryAccNo | LinkedAccNo -----------: | ----------: 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2 2 | 3 3 | 1 3 | 2 3 | 3 4 | 4
db<>fiddle here
相关文章