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

问题描述

我只是想了解 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从组

我做错了什么?

解决方案

另一种与您类似但不同之处在于:

1. 属性值包含在递归 CTE 中，以便稍后使用
2. < 用于防止重复和由此产生的无限递归
3. 添加另一个 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:

1. The property value is included in the recursive CTE so that it can be used later
2. The < is used to prevent duplicates and the resulting infinite recursion
3. 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

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