sql - 展平包含引用 SQL Server 2005 中其他行的行的表

Question

当您处理未完全规范化的表时，我遇到了一个偶尔会出现的问题。这就是问题所在。想象一个有 4 列的表，我们称这个表为 dbo.Hierarchical。这是表的定义:

if OBJECT_ID('dbo.Hierarchical') is not null
 drop table dbo.Hierarchical

create table dbo.Hierarchical
(
  colID   int   not null identity(1,1) primary key
 ,GroupName  varchar(5) not null
 ,IsAtomic  bit   not null
 ,Constituent varchar(5) null
)

这个表可以有一个GroupName是Atomic，也就是说它没有一个组件，或者不能是Atomic。在这种情况下，一个 GroupName 可以包含其他 GroupName。

为了清楚起见，让我们用一些数据填充表格。

set nocount on
insert into dbo.Hierarchical values ('A',0,'B')
insert into dbo.Hierarchical values ('A',0,'C')
insert into dbo.Hierarchical values ('B',1,'B')
insert into dbo.Hierarchical values ('C',0,'K')
insert into dbo.Hierarchical values ('C',0,'L')
insert into dbo.Hierarchical values ('D',0,'E')
insert into dbo.Hierarchical values ('D',0,'F')
insert into dbo.Hierarchical values ('D',0,'G')
insert into dbo.Hierarchical values ('E',1,'E')
insert into dbo.Hierarchical values ('F',1,'F')
insert into dbo.Hierarchical values ('G',0,'H')
insert into dbo.Hierarchical values ('G',0,'I')
insert into dbo.Hierarchical values ('H',1,'H')
insert into dbo.Hierarchical values ('I',1,'I')
insert into dbo.Hierarchical values ('J',1,'J')
insert into dbo.Hierarchical values ('K',1,'K')
insert into dbo.Hierarchical values ('L',1,'L')
insert into dbo.Hierarchical values ('M',1,'M')
insert into dbo.Hierarchical values ('N',1,'N')
set nocount off

现在，如果我们看一个简单的 select * from dbo.Hierarchical，我们会得到以下结果:

GroupName  colID      IsAtomic   Constituent
A           1         0          B
A           2         0          C
B           3         1          B
C           4         0          K
C           5         0          L
D           6         0          E
D           7         0          F
D           8         0          G
E           9         1          E
F          10         1          F
G          11         0          H
G          12         0          I
H          13         1          H
I          14         1          I
J          15         1          J
K          16         1          K
L          17         1          L
M          18         1          M
N          19         1          N

哎呀，那是啰嗦。现在，请注意前两行有 GroupName A 和 Constiuents B 和 C。B 是 Atomic，因此它没有更多的成分。然而，C 具有成分 K、L(K 和 L 是原子)。我如何创建一个 View 来展平这个表，以便我只看到 GroupName 和 Atomic constiuents。在 GroupName A 的情况下，我应该看到 3 行

A B
A K
A L

Answer 1

试一试:

--just a repeat of OP's original table and data
DECLARE @Hierarchical table
( colID   int   not null identity(1,1) primary key
 ,GroupName  varchar(5) not null
 ,IsAtomic  bit   not null
 ,Constituent varchar(5) null)
set nocount on
insert into @Hierarchical values ('A',0,'B');insert into @Hierarchical values ('A',0,'C');
insert into @Hierarchical values ('B',1,'B');insert into @Hierarchical values ('C',0,'K');
insert into @Hierarchical values ('C',0,'L');insert into @Hierarchical values ('D',0,'E');
insert into @Hierarchical values ('D',0,'F');insert into @Hierarchical values ('D',0,'G');
insert into @Hierarchical values ('E',1,'E');insert into @Hierarchical values ('F',1,'F');
insert into @Hierarchical values ('G',0,'H');insert into @Hierarchical values ('G',0,'I');
insert into @Hierarchical values ('H',1,'H');insert into @Hierarchical values ('I',1,'I');
insert into @Hierarchical values ('J',1,'J');insert into @Hierarchical values ('K',1,'K');
insert into @Hierarchical values ('L',1,'L');insert into @Hierarchical values ('M',1,'M');
insert into @Hierarchical values ('N',1,'N');set nocount off

--declare and set starting position
DECLARE @Start  varchar(5)
SET @Start='A'

--get the data
;WITH HierarchicalTree AS
(
    SELECT 
        GroupName, Constituent,  1 AS LevelOf
        FROM @Hierarchical
        WHERE GroupName=@Start
    UNION ALL
        SELECT 
            t.GroupName, h.Constituent, t.LevelOf+1
        FROM HierarchicalTree         t
            INNER JOIN @Hierarchical  h ON t.Constituent=h.GroupName
        WHERE h.Constituent!=h.GroupName AND h.IsAtomic=0
)
SELECT
    t.GroupName,t.Constituent
    FROM HierarchicalTree        t
        INNER JOIN @Hierarchical h ON t.Constituent=h.GroupName
    WHERE h.IsAtomic=1

输出:

GroupName Constituent
--------- -----------
A         B
A         K
A         L

(3 row(s) affected)