临时存储列数量可变的表

Question

假设我有一个这样的层次结构:

tbody> <<tr>11

父ID	子ID
1	2
	3
	7
3	4
3	5
3	6

我相信你基本上是在正确的轨道上。但是，由于您已经定义了列(例如，Master_Father_ID, Hierarchy和Child_ID)，因此不需要动态SQL。

我将下面的代码写成WHILE循环，而不是递归的CTE，以帮助理解发生了什么。转换为隐式CTE很容易，但我认为(特别是对许多熟悉其他编程语言的人来说)WHILE循环更熟悉。

下面的逻辑如下

1. 查找所有"Master fathers"并将其插入工作表
2. 对于上一步插入的每个值，找到它们的直接子值并将它们插入到工作台
3. 重复上述步骤2(例如，第3轮找到儿童的儿童;第4轮查找子节点的子节点)，直到没有更多的数据插入

那么最终的报告需要简单地忽略插入的初始Master_Father行(例如，在上面的表中是001、002等)。

下面的SQL也可以在这个数据库<>文件中找到—注意，我添加了额外的数据用于演示目的。

初始数据

CREATE TABLE #exploded_table (
Father_ID int, 
Child_ID int, 
PRIMARY KEY (Father_ID, Child_ID)
);
INSERT INTO #exploded_table (Father_ID, Child_ID) VALUES
(1,  2),  (1,  3),  (1,  7),  (3,  4),  (3,  5),  (3,  6);

现在对于工作表-我将其命名为#summary_table。Current_ID表示该行的当前Child_ID(例如，在层次结构的最深层)。Insert_round是插入行的整数(循环迭代)。

我也有两个变量:@round表示我们正在进行哪一轮/循环，@n表示当前轮中插入的行数。

CREATE TABLE #summary_table (
Master_Father_ID int, 
Current_ID int, 
Hierarchy nvarchar(500), 
insert_round int
);
DECLARE @round int = 1;
DECLARE @n int = 0;

步骤1:插入父节点

主父是那些本身不是子的父，例如，Father_Id 1不是子，所以包含它;Father_ID 3是子，因此不是master father。

请注意，我使用RIGHT(N'000' + LTRIM(STR(int value)), 3)将任何整数转换为3位数格式的字符串。

INSERT INTO #summary_table (Master_Father_ID, Current_ID, Hierarchy, insert_round)
SELECT  DISTINCT Father_ID, 
Father_ID, 
RIGHT(N'000' + LTRIM(STR(DENSE_RANK() OVER (ORDER BY Father_ID))), 3), 
@round
FROM    #exploded_table
WHERE   Father_ID NOT IN (SELECT Child_ID FROM #exploded_table);
SET @n = @@ROWCOUNT;

步骤2:找到最后一轮的所有子节点，并将它们的数据插入到工作表

(也是步骤3:重复，直到没有更多的发生)

从最后一轮插入(由#summary_table.insert_round标识)中获取数据，识别所有子节点并将它们的数据插入到工作表中。

一直这样做，直到你不再插入任何行。

WHILE @n > 0
BEGIN
INSERT INTO #summary_table (Master_Father_ID, Current_ID, Hierarchy, insert_round)
SELECT  DISTINCT
#summary_table.Master_Father_ID, 
#exploded_table.Child_ID, 
#summary_table.Hierarchy + N'.' + RIGHT(N'000' + LTRIM(STR(DENSE_RANK() OVER (PARTITION BY #summary_table.Master_Father_ID, #summary_table.Current_ID ORDER BY #exploded_table.Child_ID))), 3),
@round + 1
FROM    #summary_table
INNER JOIN #exploded_table ON #summary_table.Current_ID = #exploded_table.Father_ID
WHERE   #summary_table.insert_round = @round;
SET @n = @@ROWCOUNT;
SET @round += 1;

END;

最后的报告

SELECT      Master_Father_ID, Hierarchy, Current_ID AS Child_ID
FROM    #summary_table 
WHERE   insert_round > 1
ORDER BY Hierarchy;

数据如下

Master_Father_ID    Hierarchy       Child_ID
1                   001.001         2
1                   001.002         3
1                   001.002.001     4
1                   001.002.002     5
1                   001.002.003     6
1                   001.003         7

注意-这假设你的原始数据设置正确，没有循环/等(例如，如果凯尔里斯是约翰康纳的孩子以及他的父亲…额，抱歉，"剧透警报")。如果你有这些，你需要添加额外的检查——这些检查将取决于你实际想要如何处理这些循环。

使用递归CTE应该能够做到这一点。

我还包含了一个构建SQL Server层次结构数据类型(HIERARCHYID)的示例。

详情见代码注释

-- Test data based on your example
CREATE TABLE #temp( FatherID INT, ChildID INT )
INSERT INTO #temp
VALUES ( 1, 2 ), ( 1, 3 ), ( 1, 7 ), ( 3, 4 ), ( 3, 5 ), ( 3, 6 ),
-- I have added a 3rd level
( 6, 8 ), ( 6, 9 )
-- SELECT * FROM #temp;
-- Recursive CTE to get Children of Children and construct Hierarchy
;WITH Hierarchies( MasterFatherID, FatherID, ChildID, Hierarchy, Hierarchy2 )
AS(
-- This is the "anchor" part
SELECT FatherID AS MasterFatherID, FatherID, ChildID,
FORMAT( DENSE_RANK() OVER( PARTITION BY FatherID ORDER BY ChildID ), '000' ) AS Hierarchy,
-- This is an example of SQL Server built in Hierarchy data type
CAST( '/' + FORMAT( DENSE_RANK() OVER( PARTITION BY FatherID ORDER BY ChildID ), '0' ) + '/' AS HIERARCHYID ) AS Hierarchy2
FROM #temp
UNION ALL
SELECT
-- Top level Parent
Hierarchies.MasterFatherID,
-- Current Parent
t.FatherID,
-- Current Child
t.ChildID,
Hierarchies.Hierarchy + '.' + FORMAT( DENSE_RANK() OVER( PARTITION BY t.FatherID ORDER BY t.ChildID ), '000' ) AS Hierarchy,
-- This is an example of SQL Server built in Hierarchy data type
HIERARCHYID::Parse( Hierarchies.Hierarchy2.ToString() + FORMAT( DENSE_RANK() OVER( PARTITION BY t.FatherID ORDER BY t.ChildID ), '0' ) + '/' ) AS Hierarchy2
FROM Hierarchies
INNER JOIN #temp AS t ON Hierarchies.ChildID = t.FatherID
)
SELECT MasterFatherID, FatherID, ChildID, Hierarchy, Hierarchy2.ToString() AS Hierarchy2
FROM Hierarchies AS a
-- This will exclude Children (e.g. [3, 4]) from being included as their own chain
WHERE NOT EXISTS( SELECT * FROM Hierarchies AS Children WHERE Children.ChildID = a.MasterFatherID )
ORDER BY MasterFatherID

结果

MasterFatherID FatherID    ChildID     Hierarchy      Hierarchy2
-------------- ----------- ----------- -------------- ----------
1              1           2           001            /1/
1              1           3           002            /2/
1              1           7           003            /3/
1              3           4           002.001        /2/1/
1              3           5           002.002        /2/2/
1              3           6           002.003        /2/3/
1              6           8           002.003.001    /2/3/1/
1              6           9           002.003.002    /2/3/2/