时间戳

首页 > 解决方案 > 查找 SQL 表中的最低递归

sql - 查找 SQL 表中的最低递归

问题描述

我需要获取层次结构数据中的最低父级。我在获取最低父 ID 时遇到问题。请参阅下面的查询和预期结果。对此的任何帮助表示赞赏。

 CREATE TABLE #Modules ([ModuleId] INT, [ModuleName] VARCHAR(50) NOT NULL, [ParentId] INT NULL)

INSERT INTO #Modules
SELECT 1, 'Master', NULL
UNION ALL SELECT 2, 'UsersGroup2', 1
UNION ALL SELECT 3, 'UsersGroup3', 2
UNION ALL SELECT 4, 'UsersGroup4', 3
UNION ALL SELECT 5, 'UsersGroup5', 4
UNION ALL SELECT 6, 'UsersGroup6', 5
UNION ALL SELECT 7, 'UsersGroup7', 5
UNION ALL SELECT 8, 'UsersGroup8', NULL
UNION ALL SELECT 9, 'UsersGroup9', NULL
UNION ALL SELECT 10, 'UsersGroup10', 9
UNION ALL SELECT 11, 'UsersGroup11', 10
UNION ALL SELECT 12, 'UsersGroup12', 11
UNION ALL SELECT 14, 'UsersGroup14', 12
UNION ALL SELECT 15, 'UsersGroup15', 9


;WITH CTE AS (SELECT 1 AS [Level], [ModuleId], 
             [ModuleName] AS [GrandParent], [ModuleName], [ParentId]
             FROM #Modules
             WHERE [ParentId] IS NULL
             UNION ALL SELECT cycle.[Level] + 1, base.[ModuleId], 
             cycle.[GrandParent], base.[ModuleName], base.[ParentId]
             FROM #Modules base
             INNER JOIN CTE cycle ON cycle.[ModuleId] = base.[ParentId])
SELECT CTE.ModuleId, CTE.ParentId AS GrandParentId, COALESCE(NULLIF([GrandParent],[ModuleName])+'-->'+[ModuleName],[ModuleName]) AS [ModuleName]
FROM CTE

DROP TABLE #Modules

结果：

ModuleId    GrandParentId     ModuleName
1            NULL             Master
8            NULL             UsersGroup8
9            NULL             UsersGroup9
10            9               UsersGroup9-->UsersGroup10
15            9               UsersGroup9-->UsersGroup15
11            9               UsersGroup9-->UsersGroup11
12            9               UsersGroup9-->UsersGroup12
14            9               UsersGroup9-->UsersGroup14
2             1               Master-->UsersGroup2
3             1               Master-->UsersGroup3
4             1               Master-->UsersGroup4
5             1               Master-->UsersGroup5
6             1               Master-->UsersGroup6
7             1               Master-->UsersGroup7

标签： sqlrecursive-query

解决方案

您在递归 CTE 中将 ParentID 作为 GrandParent 返回。当您重新加入 CTE 以获得所有级别时，这种情况正在发生变化。

如果您在顶层也定义了 GrandParentID 字段并在 CTE 的第二层中引用它，您将得到答案：

with data1 as(
SELECT 1 [ModuleId], 'Master' [ModuleName], NULL [ParentId]
UNION ALL SELECT 2, 'UsersGroup2', 1
UNION ALL SELECT 3, 'UsersGroup3', 2
UNION ALL SELECT 4, 'UsersGroup4', 3
UNION ALL SELECT 5, 'UsersGroup5', 4
UNION ALL SELECT 6, 'UsersGroup6', 5
UNION ALL SELECT 7, 'UsersGroup7', 5
UNION ALL SELECT 8, 'UsersGroup8', NULL
UNION ALL SELECT 9, 'UsersGroup9', NULL
UNION ALL SELECT 10, 'UsersGroup10', 9
UNION ALL SELECT 11, 'UsersGroup11', 10
UNION ALL SELECT 12, 'UsersGroup12', 11
UNION ALL SELECT 14, 'UsersGroup14', 12
UNION ALL SELECT 15, 'UsersGroup15', 9
)

, CTE AS (SELECT 1 AS [Level], [ModuleId], [ModuleId] GrandParentID ,
             [ModuleName] AS [GrandParent], [ModuleName], [ParentId]
             FROM data1
             WHERE [ParentId] IS NULL
             UNION ALL 
             SELECT cycle.[Level] + 1, 
             base.[ModuleId], 
             cycle.GrandParentID,
             cycle.[GrandParent], 
             base.[ModuleName], 
             base.[ParentId]
             FROM data1 base
             INNER JOIN CTE cycle ON cycle.[ModuleId] = base.[ParentId])
SELECT CTE.ModuleId, GrandParentId,COALESCE(NULLIF([GrandParent],[ModuleName])+'-->'+[ModuleName],[ModuleName]) AS [ModuleName]
FROM CTE

或以您的格式：

WITH CTE AS (SELECT 1 AS [Level], [ModuleId], [ModuleId] GrandParentID, 
         [ModuleName] AS [GrandParent], [ModuleName], [ParentId]
         FROM #Modules
         WHERE [ParentId] IS NULL
         UNION ALL SELECT cycle.[Level] + 1, base.[ModuleId], 
         cycle.[GrandParent], base.[ModuleName], base.[ParentId]
         FROM #Modules base
         INNER JOIN CTE cycle ON cycle.[ModuleId] = base.[ParentId])
SELECT CTE.ModuleId, CTE.GrandParentId, COALESCE(NULLIF([GrandParent],[ModuleName])+'-->'+[ModuleName],[ModuleName]) AS [ModuleName]
FROM CTE

推荐阅读