首页 文章

递归CTE（T-SQL）返回意外结果

提问于
浏览 次
2

我一直在为这个代码盯着WAY太长时间，试图弄清楚为什么我的最终查询会返回意外的结果 .

任何帮助将非常感激 . 提前致谢 .

给出以下代码（在SQL Server 2008 R2上运行）：

USE tempdb;

DECLARE @emp--loyee
TABLE (
    EmployeeID int NOT NULL
    ,EmployeeName nvarchar(50) NOT NULL
    PRIMARY KEY(EmployeeID)
)

INSERT INTO @emp
SELECT 1,'Fred'
UNION
SELECT 2,'Mary'
UNION
SELECT 3,'Joe'
UNION
SELECT 4,'Bill'

DECLARE @grp TABLE (
    GroupID int NOT NULL
    ,GroupName nvarchar(50)
    PRIMARY KEY(GroupID)
)

INSERT INTO @grp
SELECT 1,'Group 1'
UNION
SELECT 2,'Group 2'
UNION
SELECT 3,'Group 3'


DECLARE @empgrp TABLE (
    EmployeeID int NOT NULL
    ,GroupID int NOT NULL
    PRIMARY KEY (EmployeeID,GroupID)
)

INSERT INTO @empgrp
SELECT 1,1
UNION
SELECT 2,1
UNION
SELECT 3,1
UNION
SELECT 4,2

DECLARE @grpgrp TABLE (
    GroupID int NOT NULL
    ,ParentGroupID int
    ,UNIQUE CLUSTERED(GroupID,ParentGroupID)
)

INSERT INTO @grpgrp
SELECT 1,2
UNION
SELECT 2,3;


WITH AllEmpGroups (EmployeeID,GroupID,RootGroupID)
AS
(
    SELECT CAST(NULL as int) as EmployeeID,pgrp.GroupID,pgrp.ParentGroupID
    FROM @grpgrp pgrp LEFT JOIN @grpgrp ggrp
    ON pgrp.ParentGroupID = ggrp.GroupID
    UNION ALL
    SELECT e.EmployeeID,eg.GroupID,aeg.RootGroupID
    FROM @emp e JOIN @empgrp eg
    ON e.EmployeeID = eg.EmployeeID
    JOIN @grpgrp ggrp
    ON eg.GroupID = ggrp.GroupID
    JOIN AllEmpGroups aeg
    ON aeg.GroupID = ggrp.ParentGroupID
)

SELECT EmployeeID,GroupID,RootGroupID
FROM AllEmpGroups

我得到的是：

+------------+---------+-------------+
| EmployeeID | GroupID | RootGroupID |
+------------+---------+-------------+
| NULL       |       1 |           2 |
| NULL       |       2 |           3 |
| 1          |       1 |           3 |
| 2          |       1 |           3 |
| 3          |       1 |           3 |
+------------+---------+-------------+

我期望/想要得到的是：

+------------+---------+-------------+
| EmployeeID | GroupID | RootGroupID |
+------------+---------+-------------+
| NULL       |       1 |           2 |
| NULL       |       2 |           3 |
| 4          |       2 |           3 |
| 1          |       1 |           3 |
| 2          |       1 |           3 |
| 3          |       1 |           3 |
+------------+---------+-------------+

最重要的是，我想要在给定根组下面的所有员工的完整递归堆栈，每行上都有根组ID .

我错过了什么？

sql sql-server tsql recursion recursive-query

2 回答

  • 2

    第一：

    • @grpgrp 中的根节点需要一行，其值为 3, null

    • 递归cte的锚点（ union all 之前的部分）需要是祖先第一次递归的根节点（ 3, null ） .

    ...

INSERT INTO @grpgrp
SELECT 1,2
UNION all
SELECT 2,3
UNION all
select 3, null;

WITH AllEmpGroups (EmployeeID,GroupID,RootGroupID)
AS
(
    SELECT CAST(NULL as int) as EmployeeID,pgrp.GroupID, ParentGroupID = pgrp.GroupID
    FROM @grpgrp pgrp LEFT JOIN @grpgrp ggrp
      ON pgrp.ParentGroupID = ggrp.GroupID
    where pgrp.ParentGroupId is null
    UNION ALL
    SELECT e.EmployeeID,eg.GroupID,aeg.RootGroupID
    FROM @emp e JOIN @empgrp eg
    ON e.EmployeeID = eg.EmployeeID
    JOIN @grpgrp ggrp
    ON eg.GroupID = ggrp.GroupID
    JOIN AllEmpGroups aeg
    ON aeg.GroupID = ggrp.ParentGroupID
)

SELECT EmployeeID,GroupID,RootGroupID
FROM AllEmpGroups

    rextester演示：http://rextester.com/CBWY80387

    收益：

    +------------+---------+-------------+
| EmployeeID | GroupID | RootGroupID |
+------------+---------+-------------+
| NULL       |       3 |           3 |
| 4          |       2 |           3 |
| 1          |       1 |           3 |
| 2          |       1 |           3 |
| 3          |       1 |           3 |
+------------+---------+-------------+

    除此之外，我将首先构建组层次结构，然后像这样加入员工：

    WITH AllEmpGroups (GroupID,ParentGroupID,RootGroupID)
AS
(
    SELECT pgrp.GroupID, pgrp.ParentGroupID, RootGroupId = GroupID
    FROM @grpgrp pgrp 
    where pgrp.ParentGroupId is null
    UNION ALL
    SELECT ggrp.GroupID,ggrp.ParentGroupID,aeg.RootGroupID
    FROM  @grpgrp ggrp
    inner JOIN AllEmpGroups aeg
        ON aeg.GroupID = ggrp.ParentGroupID

)
SELECT eg.EmployeeID,aeg.*
FROM AllEmpGroups aeg
    left JOIN @empgrp eg 
        ON eg.GroupID = aeg.GroupID

    rextester演示：http://rextester.com/FAK76354

    收益：

    +------------+---------+---------------+-------------+
| EmployeeID | GroupID | ParentGroupID | RootGroupID |
+------------+---------+---------------+-------------+
| NULL       |       3 | NULL          |           3 |
| 4          |       2 | 3             |           3 |
| 1          |       1 | 2             |           3 |
| 2          |       1 | 2             |           3 |
| 3          |       1 | 2             |           3 |
+------------+---------+---------------+-------------+
    回复于
  • 2

    从...开始

    WITH AllGroups (RootGroupID,GroupID,ParentGroupID, level)
AS
(
    SELECT GroupID RootGroupID, GroupID, Cast(NULL as int) ParentGroupID, 0 level
    FROM @grp g
    WHERE NOT EXISTS (SELECT 1 FROM @grpgrp gg WHERE gg.GroupID = g.GroupID)

    UNION ALL
    SELECT ag.RootGroupID, gg.GroupID, gg.ParentGroupID, level+1
    FROM @grpgrp gg
    JOIN AllGroups ag
    ON ag.GroupID = gg.ParentGroupID
)


SELECT EmployeeID, ag.GroupID, ParentGroupID, RootGroupID
FROM AllGroups ag
LEFT JOIN  @empgrp eg ON eg.GroupID = ag.GroupID
ORDER BY RootGroupID, level, ParentGroupID, GroupID;

    不确定为什么你需要这一行：

    | NULL       |       2 |           3 |
    回复于

相关问题