用于展平记录层次结构的 Sql 服务器查询

Question

我有一个描述层次结构的表：

Name    MemberName
A       B
A       C
B       D
D       E
F       G

MemberName 引用同一个表的 Name 列。从这个表中，我可以很容易地查询到B和C是A内的成员，D是B的成员，E是D的成员，G是F的成员。

基于这个结构，很难编写查询这表明 D 和 E 也是 A 的间接成员。D 和 E 也是 B 的间接成员，依此类推。所以我需要做的是建立一个新表来显示所有间接成员。因此，对于上面的表数据，我最终会得到一个表，其中包含：

Name    MemberName
A       B
A       C
A       D
A       E
B       D
B       E
D       E
F       G

我首先将不属于其他记录（顶级）记录的成员的所有记录放入临时表中：

CREATE TABLE #TMP
(
    [Name] varchar(20),
    [MemberName] varchar(20)
)

DECLARE @iRowsFound INT
INSERT INTO #TMP ([Name],[MemberName]) 
(SELECT * FROM [HierarchyData] WHERE [Name] NOT IN 
   (SELECT [MemberName] FROM [HierarchyData]))
SELECT @iRowsFound = @@ROWCOUNT

Name    MemberName
A       B
A       C
F       G

然后我的理论是， 交叉联接中的适用记录插入回临时表中，然后执行该 while 循环，直到交叉联接中没有更多适用记录可插入：

WHILE (@iRowsFound > 0)
BEGIN
    INSERT INTO #TMP ([Name],[MemberName]) 
    (
        SELECT 
            [NewName] = ??,
            [NewMember] = ??
        FROM
            [HierarchyData],[#TMP]
        WHERE
            ???        
    )
    SELECT @iRowsFound = @@ROWCOUNT
END

while 循环，将临时表交叉联接到 heirachy 表，并将 只是不确定我是否走在正确的轨道上，因为我对交叉连接选择应该是什么样子有点困惑。有没有人做过类似的事情（在sql server 2000中）？

编辑：我想我可能已经明白了： - 虽然我很确定一定有一个 更有效的方法来做到这一点......

WHILE (@iRowsFound > 0)
BEGIN
    INSERT INTO #TMP ([Name],[MemberName]) 
    (       
            SELECT
                --[#TMP].[Name],
                --[#TMP].[MemberName],
                [HierarchyData].[Name],
                [HierarchyData].[MemberName]
            FROM 
                [#TMP]
            JOIN 
                [HierarchyData] ON [#TMP].[MemberName] = [HierarchyData].[Name]
            --WHERE
            --  [#TMP].[MemberName] = [HierarchyData].[Name]
            AND NOT EXISTS (SELECT * FROM [#TMP] WHERE [#TMP].[Name] = [HierarchyData].[Name] AND [#TMP].[MemberName] = [HierarchyData].[MemberName])   
            UNION   
            SELECT
                [#TMP].[Name],
                --[#TMP].[MemberName],
                --[HierarchyData].[Name],
                [HierarchyData].[MemberName]
            FROM 
                [#TMP]
            JOIN 
                [HierarchyData] ON [#TMP].[MemberName] = [HierarchyData].[Name]     
            AND NOT EXISTS (SELECT * FROM [#TMP] WHERE [#TMP].[Name] = [#TMP].[Name] AND [#TMP].[MemberName] = [HierarchyData].[MemberName])    

    )
    SELECT @iRowsFound = @@ROWCOUNT
END

Answer 1

很遗憾你不在sql server 2005或更高版本上，使用递归CTE很容易，代码在这里：

WITH Members AS
(
  Select Name, MemberName 
  FROM HierarchyData
  UNION ALL
  SELECT Name, Child.MemberName as [MemberName]
  FROM Members
  JOIN HierarchyData Child ON Members.MemberName = Child.Name
)
SELECT * FROM Members

在2000年，你可以基本上以相同的方式完成它（将最后一个选择的结果连接到原始表，直到你没有循环内最后一组的结果），但这要困难得多，因为您必须通过计数器跟踪您正在进行的迭代。恶心。

这有帮助吗，或者您想要一些 sql 2000 伪代码吗？

更好的是，只需升级！

Answer 2

这是 SQL 2000 版本。

一些注意事项：这适用于任意数量的级别，并且不会出现循环错误（就像 CTE 版本那样。）

declare @lastcount int
declare @lastcycle int

Select HierarchyData.Name, HierarchyData.MemberName, 0 as [Cycle] INTO #list
FROM HierarchyData

SET @lastcount = @@rowcount
SET @lastcycle = 0

while @lastcount > 0
BEGIN
  INSERT INTO #list
    SELECT Members.Name, Child.MemberName as [MemberName], @lastcycle+1 as [Cycle]
    FROM #list Members
    JOIN HierarchyData Child ON Members.MemberName = Child.Name
    LEFT JOIN #list cycletest ON Members.Name = cycletest.Name AND Child.MemberName = cycletest.Membername
    WHERE Members.Cycle = @lastcycle AND NOT (Members.Name = Child.MemberName) AND cycletest.Name is null

  SET @lastcount = @@rowcount

  SET @lastcycle = @lastcycle + 1
END

SELECT [Name], [MemberName] FROM #list
ORDER BY [Name], [MemberName]

DROP TABLE #list

---- Test data
--create table HierarchyData
--(
--  [Name] varchar(20),
--  [MemberName] varchar(20)
--)
--
--INSERT INTO HierarchyData (Name,MemberName) Values('A','B')
--INSERT INTO HierarchyData (Name,MemberName) Values('A','C')
--INSERT INTO HierarchyData (Name,MemberName) Values('B','D')
--INSERT INTO HierarchyData (Name,MemberName) Values('D','E')
--INSERT INTO HierarchyData (Name,MemberName) Values('F','G')
----CYCLE TEST  (the CTE will not work)
--INSERT INTO HierarchyData (Name,MemberName) Values('E','D')
--
---- Test
--select * from HierarchyData

---- CTE Works (note, will fail on cycles.)
--WITH Members AS
--(
--  Select HierarchyData.Name, HierarchyData.MemberName 
--  FROM HierarchyData
--  UNION ALL
--  SELECT Members.Name, Child.MemberName as [MemberName]
--  FROM Members
--  JOIN HierarchyData Child ON Members.MemberName = Child.Name
--)
--SELECT * FROM Members
--ORDER BY [Name], [MemberName]

Answer 3

我使用以下代码模式来遵循 SQL Server 2000 中的层次结构。“神奇”是将深度值添加到临时表中，以便您可以在 WHERE 子句中使用它。

SET NOCOUNT ON

CREATE TABLE #super_trees
(
    supervisor_uid  INTEGER,
    actor_uid       INTEGER,
    depth           INTEGER
)

DECLARE
    @more_users BIT,
    @depth      INTEGER

SET @more_users = 1
SET @depth      = 0

INSERT INTO #super_trees VALUES (@supervisor_uid, @supervisor_uid, @depth)

SET @depth = @depth + 1

WHILE (@more_users = 1)
BEGIN

    INSERT INTO #super_trees (supervisor_uid, actor_uid, depth)
        SELECT u.supervisor_uid,
               u.actor_uid,
               @depth
          FROM #super_trees sr
           INNER JOIN
           dbo.users u
           ON (sr.actor_uid = u.supervisor_uid)
         WHERE sr.depth = (@depth - 1)

    IF @@ROWCOUNT < 1
        SET @more_users = 0

    SET @depth = @depth + 1

END

Answer 4

使用上述 CTE 不符合海报的目的。他/她想要扁平化数据。 CTE 仅返回 ParentID 列下具有不同值的层次结构信息。

姓名 会员姓名
AB
交流电
BD
德
FG

所以上面是您使用 CTE 得到的结果，而不是

Name MemberName
AB
交流电
广告
AE
BD
是
德
FG

Answer 5

我建议您对您的数据稍作修改。您没有记录表明 A 是层次结构的根。添加：

INSERT INTO #TMP(Name, MemberName) VALUES (NULL, 'A') 

大大简化了事情（而且，通常情况下，邻接列表将以“相反的方式”表示：列 Name 和列 ParentName ，它们对应于分别是您的 MemberName 和 Name 列

通过该设置，您可以使用通用表表达式来完成这项工作：

WITH Node (Name, ParentName)
AS  (
    SELECT     Name, ParentName
    FROM       Tab
    WHERE      ParentName IS NULL
    UNION ALL
    SELECT     Tab.Name, Tab.ParentName
    FROM       Tab
    INNER JOIN Node
    ON         ParentName = Node.Name
    )
SELECT Name, ParentName
FROM   Node

不幸的是，通用表表达式是受支持的。正如 Hogan 所指出的，在 MS SQL 2005 及更高版本中。