如何从自引用表中获取子数据（SQL）

Question

如何从自引用表中获取子数据（SQL）

sql-serverssrs-2012

3

我正在使用SQL 2012，有一个自引用表格，有3个级别。表结构如下：

表结构

我还有另一个引用此表的表，它有ID作为外键，因此如果到该表的外键为6，则需要显示6为“AAA”，并且它是“AA”的子节点，后者是“A”的子节点。我需要钻取到较低级别，并从较低级别向上升级到上一级别。目前，我可以升级到第二级。

以下是引用其他表的表的结构。

因此，我想报告这两个表，最终输出应如下所示：

如果我的问题不太清楚，请问我会尽力澄清。

- Tooleh

1

English:Programming languages are used to develop software, mobile applications, websites, and other computer programs.Chinese:编程语言用于开发软件、移动应用程序、网站和其他计算机程序。 - ADyson

可能是使用CTE递归获取树形层次结构的重复问题。 - iamdave

为什么不使用 hierarchyid？层级数量固定吗？如果是这样，您无需使用递归或CTE，只需进行3个自连接即可。代码将比CTE更简单，性能更快。 - Panagiotis Kanavos

不清楚“CategoryName”、“CategoryType”和“SubCategory”。什么是“Type”？ - anatol

这是CategoryName的子级，如果Category Name是A，则CategoryType为AA，SubCategory将是AAA。对于糟糕的命名表示抱歉。 - Tooleh

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user1429080 · Accepted Answer

假设类别树的深度不超过3级，这应该有效：

declare @Catergory table (
    ID int not null,
    Name nvarchar(10) not null,
    ParentID int null
)
declare @Customer table (
    ID int not null,
    Name nvarchar(10) not null,
    SurName nvarchar(10) not null,
    Address nvarchar(30) not null,
    CategoryId int not null
)
insert into @Catergory (ID, Name, ParentID)
values (1, 'A', null), (2, 'B', null),
    (3, 'C', null), (4, 'AA', 1),
    (5, 'CC', 3), (6, 'AAA', 4),
    (7, 'BB', 2), (8, 'AAA', 4),
    (9, 'CCC', 5), (10, 'AA', 1)

insert into @Customer (ID, Name, SurName, Address, CategoryId)
values (1, 'Duck', 'Duffy', '10 Steet', 10),
 (2, 'Ben', 'Ten', '10 Steet', 6),
 (3, 'Cat', 'Dog', '10 Steet', 5),
 (4, 'Chicken', 'Wings', '10 Steet', 1),
 (5, 'Fish', 'Water', '10 Steet', 7)

-- build structure using assumption that the depth is max three levels

select *
from @Customer cust
    join (
        select ID, Name as CategoryName, null As CategoryType, null as SubCategory from @Catergory roots where ParentID is null
        union
        select mids.ID, roots.Name, mids.Name, null from @Catergory mids
            join @Catergory roots on mids.ParentID = roots.ID and roots.ParentID is null
        union
        select leafs.ID, roots.Name, mids.Name, leafs.Name from @Catergory leafs
            join @Catergory mids on leafs.ParentID = mids.ID 
            join @Catergory roots on mids.ParentID = roots.ID and roots.ParentID is null
    ) as struct on cust.CategoryId = struct.ID
order by cust.id

输出：

+----+---------+---------+----------+------------+----+--------------+--------------+-------------+
| ID |  Name   | SurName | Address  | CategoryId | ID | CategoryName | CategoryType | SubCategory |
+----+---------+---------+----------+------------+----+--------------+--------------+-------------+
|  1 | Duck    | Duffy   | 10 Steet |         10 | 10 | A            | AA           | NULL        |
|  2 | Ben     | Ten     | 10 Steet |          6 |  6 | A            | AA           | AAA         |
|  3 | Cat     | Dog     | 10 Steet |          5 |  5 | C            | CC           | NULL        |
|  4 | Chicken | Wings   | 10 Steet |          1 |  1 | A            | NULL         | NULL        |
|  5 | Fish    | Water   | 10 Steet |          7 |  7 | B            | BB           | NULL        |
+----+---------+---------+----------+------------+----+--------------+--------------+-------------+

一些额外的列仍然存在，但我相信你可以将它们清除掉。请注意，其中一些类别列具有null值。这是因为如果客户处于高级别或中级别类别中，则没有合理的方法来填写这些列。