SQL Server 递归自联接 [英] SQL Server recursive self join

问题描述

我有一个简单的类别表，包括以下列:

I have a simple categories table as with the following columns:

• 身份证
• 姓名
• ParentId

因此，无限数量的类别可以是类别的子项.以以下层次结构为例:

So, an infinite amount of Categories can be the child of a category. Take for example the following hierarchy:

我希望，在返回类别商务笔记本电脑"的简单查询中，还返回包含所有父项、逗号分隔符或其他内容的列:

I want, in a simple query that returns the category "Business Laptops" to also return a column with all it's parents, comma separator or something:

或者举个例子:

推荐答案

递归 cte 来救援....

Recursive cte to the rescue....

创建并填充示例表(请在您以后的问题中保存我们这一步):

Create and populate sample table (Please save us this step in your future questions):

DECLARE @T as table
(
    id int,
    name varchar(100),
    parent_id int
)

INSERT INTO @T VALUES
(1, 'A', NULL),
(2, 'A.1', 1),
(3, 'A.2', 1),
(4, 'A.1.1', 2),
(5, 'B', NULL),
(6, 'B.1', 5),
(7, 'B.1.1', 6),
(8, 'B.2', 5),
(9, 'A.1.1.1', 4),
(10, 'A.1.1.2', 4)

CTE:

;WITH CTE AS
(
    SELECT id, name, name as path, parent_id
    FROM @T 
    WHERE parent_id IS NULL
    UNION ALL
    SELECT t.id, t.name, cast(cte.path +','+ t.name as varchar(100)), t.parent_id
    FROM @T t
    INNER JOIN CTE ON t.parent_id = CTE.id
)

查询:

SELECT id, name, path
FROM CTE

结果:

id      name        path
1       A           A
5       B           B
6       B.1         B,B.1
8       B.2         B,B.2
7       B.1.1       B,B.1,B.1.1
2       A.1         A,A.1
3       A.2         A,A.2
4       A.1.1       A,A.1,A.1.1
9       A.1.1.1     A,A.1,A.1.1,A.1.1.1
10      A.1.1.2     A,A.1,A.1.1,A.1.1.2

查看关于 rextester 的在线演示

这篇关于SQL Server 递归自联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！