SQL Server 2008 r2 - 如何将用于生成菜单层次结构的过程 SQL 代码转换为基于 SET 的方法

Question

我有一个SQL Server 2008 R2数据库表，其中包含以下格式的层次结构信息：

MarketID    Time                    menuPath                                                       SID  MarketName
107397507   2012-11-18 13:00:00.000 Project 1Phase 1Project MgmtDate 18 NovemberRequirements   1   Meeting
107397508   2012-11-18 13:00:00.000 Project 1Phase 1Project MgmtDate 18 NovemberRequirements   1   Plan
145556789   2012-11-20 12:00:00.000 Project 2Phase 3TrainingDate 20 November                    3   Verbal
145686775   2012-11-20 15:00:00.000 Project 2Phase 4TestingDate 20 November                     3   Structural
145686776   2012-11-20 15:00:00.000 Project 2Phase 4TestingDate 20 November                     3   Optical

所需的层次结构输出如下：

ID  ParentID    Depth   Name          MarketID
1   0           0       Project 1        NULL
2   1           1       Phase 1          NULL
3   2           2       Project Mgmt     NULL
4   3           3       18 November      NULL
5   4           4       Requirements     NULL
6   5           5       Meeting          107397507
7   5           5       Plan             107397508
8   0           0       Project 2        NULL
9   8           1       Phase 3          NULL
10  9           2       Training         NULL
11  10          3       20 November      NULL
12  11          4       12:00 Verbal     145556789
13  8           1       Phase 4          NULL
14  13          2       Testing          NULL
15  14          3       20 November      NULL
16  15          4       15:00 Structural 145686775
17  15          4       15:00 Optical    145686776

注意："日期"一词已从节点"11 月 18 日"中删除

• 编辑：仅输出唯一的父子节点，例如，只有一个"项目1\阶段1"节点，但有两个"11月20日"节点：一个是"训练\11月20日"，另一个是"测试\20十一月'。
• 编辑：对于SID = 3的所有节点，必须将时间添加到最后一个节点，例如，"测试\11月20日\15：00光学"
• 编辑：菜单路径字段中包含的确切深度可能会有所不同。

我已经能够创建以下过程SQL查询来完成此操作，但是有谁知道我如何将其转换为等效的基于集的方法？

/* Begin build of Menu table */
Declare @marketid int
Declare @Time DATETIME
DECLARE @StrMenu NVARCHAR(MAX)
DECLARE @SID INT
DECLARE @StrMarketName NVARCHAR(MAX)
DECLARE @selection VARCHAR(MAX)
DECLARE @parentname VARCHAR(MAX)
DECLARE @parentid INT
DECLARE @depth INT
DECLARE @boolDate INT
DECLARE @EIND INT 
DECLARE @Part NVARCHAR(MAX)
DECLARE @IND    INT
DECLARE cur CURSOR LOCAL for
    SELECT MarketID, Time, menuPath, SID, MarketName FROM test.dbo.Markets
OPEN cur
fetch next from cur into @marketid, @Time, @StrMenu, @SID, @StrMarketName
while @@FETCH_STATUS = 0 BEGIN
    SET @IND = CHARINDEX('',@StrMenu)
    -- if the last character is not a  then append it to the string
    IF RIGHT(@StrMenu,1) != ''
        BEGIN
            SET @StrMenu = @StrMenu + ''
        END
    IF @SID = 3
        BEGIN
        -- IF SID = 3 then append the Time to the MarketName
            SET @StrMarketName = (convert(varchar(5), @Time, 108)) + ' ' + @StrMarketName
        END
    SET @StrMenu = @StrMenu + @StrMarketName + ''
    Set @EIND = 0
    SET @boolDate = 0
    SET @depth = 0
    WHILE(@IND != LEN(@StrMenu))
        BEGIN
            SET  @EIND = ISNULL(((CHARINDEX('', @StrMenu, @IND + 1)) - @IND - 1), 0)
            SET @selection = (SUBSTRING(@StrMenu, (@IND  + 1),  @EIND))
            IF @depth = 0
                BEGIN
                    SET @parentid = 0
                END
            IF @depth > 0
                BEGIN
                    SET @parentid = (SELECT TOP 1 ID FROM test.dbo.Menu WHERE NAME = @parentname ORDER BY ID DESC )
                END
            IF (@selection LIKE '%Date%')
                BEGIN
                    SET @boolDate = 1
                    SET @selection = REPLACE(@selection, 'Date ', '')
                    SET @parentid = (SELECT ID FROM test.dbo.Menu WHERE NAME = @parentname )
                    -- insert values into the menu table
                    IF NOT EXISTS (SELECT NAME FROM test.dbo.Menu WHERE NAME = @selection AND ParentID = @parentid)
                        INSERT INTO test.dbo.Menu (ParentID, Depth, Name)
                        Values (@parentid, @depth, @selection)
                END
            -- only continue if the selection and its parent combination does not already exist
            IF NOT EXISTS (SELECT ID FROM test.dbo.Menu WHERE NAME = @selection AND ParentID = @parentid) AND @boolDate = 0
                BEGIN                   
                    IF (LEN(@StrMenu) = @EIND + @IND + 1)
                        BEGIN
                            -- If the current loop is the last loop then insert the MarketID
                            INSERT INTO test.dbo.Menu (ParentID, Depth, Name, MarketID)
                            Values (@parentid, @depth, @selection, @marketid)
                        END
                    Else
                        BEGIN
                            -- Otherwise only insert the basic info into the menu table
                            INSERT INTO test.dbo.Menu (ParentID, Depth, Name)
                            Values (@parentid, @depth, @selection)
                        END
                END
            SET @boolDate = 0
            -- increment the index values and set the parent name for the next loop
            SET @IND = ISNULL(CHARINDEX('', @StrMenu, @IND + 1), 0)
            SET @depth = @depth + 1
            SET @parentname = @selection
        END
    fetch next from cur into @marketid, @Time, @StrMenu, @SID, @StrMarketName
END
close cur
deallocate cur

我编写此 SQL 是为了从menuPath列中提取层次结构信息。根据 SID 编号，MarketName信息和Time列也会附加到此menuPath例如，如果 SID = 1，则仅附加MarketName，但如果 SID = 3，则附加Time和MarkeName。

该MarketID仅添加到MarketName节点的菜单表中。

我正在使用的表架构和数据的示例如下：

    USE [test]
GO
CREATE TABLE [dbo].[Markets](
    [MarketID] [int] PRIMARY KEY NOT NULL,
    [Time] [datetime] NULL,
    [menuPath] [varchar](255) NULL,
    [SID] [int] NULL,
    [MarketName] [varchar](255) NULL
    )
CREATE TABLE [dbo].[Menu](
    [ID] [int] PRIMARY KEY IDENTITY,
    [ParentID] [int] NOT NULL,
    [Depth] [int] NOT NULL,
    [Name] [varchar] (255) NOT NULL,
    [MarketID] [int] NULL
 )
INSERT Markets (MarketID, Time, menuPath, SID, MarketName)
SELECT 107397507, '2012-11-18 13:00:00.000', 'Project 1Phase 1Project MgmtDate 18 NovemberRequirements', 1, 'Meeting'
UNION ALL SELECT 107397508, '2012-11-18 13:00:00.000', 'Project 1Phase 1Project MgmtDate 18 NovemberRequirements', 1, 'Plan'
UNION ALL SELECT 107397509, '2012-11-18 13:00:00.000', 'Project 1Phase 1Project MgmtDate 18 NovemberRequirements', 1, 'Write Up'
UNION ALL SELECT 107397513, '2012-11-18 13:00:00.000', 'Project 1Phase 1Project MgmtDate 18 NovemberBuilding 1', 1, 'Plan'
UNION ALL SELECT 107397514, '2012-11-18 13:00:00.000', 'Project 1Phase 1Project MgmtDate 18 NovemberBuilding 1', 1, 'Write Up'
UNION ALL SELECT 107397533, '2012-11-19 14:30:00.000', 'Project 1Phase 1Project MgmtDate 19 NovemberBuilding 2', 1, 'Plan'
UNION ALL SELECT 107397537, '2012-11-19 14:30:00.000', 'Project 1Phase 1Project MgmtDate 19 NovemberBuilding 2', 1, 'Write Up'
UNION ALL SELECT 107398573, '2012-11-20 09:00:00.000', 'Project 1Phase 1InstallationDate 20 NovemberBuilding 3', 1, 'Plan'
UNION ALL SELECT 107398574, '2012-11-20 09:00:00.000', 'Project 1Phase 1InstallationDate 20 NovemberBuilding 3', 1, 'Write Up'
UNION ALL SELECT 108977458, '2012-11-21 10:00:00.000', 'Project 1Phase 2SetupDate 21 NovemberBuilding 4', 1, 'Prep'
UNION ALL SELECT 108977459, '2012-11-21 10:00:00.000', 'Project 1Phase 2SetupDate 21 NovemberBuilding 4', 1, 'Clear'
UNION ALL SELECT 145556788, '2012-11-20 12:00:00.000', 'Project 2Phase 3TrainingDate 20 November', 3, 'Written'
UNION ALL SELECT 145556789, '2012-11-20 12:00:00.000', 'Project 2Phase 3TrainingDate 20 November', 3, 'Verbal'
UNION ALL SELECT 145686775, '2012-11-21 15:00:00.000', 'Project 2Phase 4TestingDate 21 November', 3, 'Structural'
UNION ALL SELECT 145686776, '2012-11-21 15:00:00.000', 'Project 2Phase 4TestingDate 21 November', 3, 'Optical'

Answer 1

我会使用 XML 扩展将

路径拆分为其组件 parst，您还可以使用 xml 扩展来获取每个 xml 元素的位置，它与 ROW_NUMBER 相结合（以考虑开始时的空节点）为您提供depth字段：

WITH Menus AS
(   SELECT  m.MarketID,
            [Name] = y.value('.', 'nvarchar(max)'),
            [Depth] = ROW_NUMBER() OVER(PARTITION BY MarketID ORDER BY y.value('for $i in . return count(../*[. << $i]) + 1', 'int')) - 1
    FROM    Markets m
            CROSS APPLY (VALUES (CAST('<x><y>' + REPLACE(menuPath, '', '</y><y>') + '</y><y>' + CASE WHEN SID = 3 THEN CONVERT(VARCHAR(5), [Time], 8) + ' ' ELSE '' END +  marketName + '</y></x>' AS XML))) a (x)
            CROSS APPLY x.nodes('/x/y') b (y)
    WHERE   y.value('.', 'nvarchar(max)') != ''
)
SELECT  MarketID,
        [Name] = CASE WHEN LEFT(Name, 5) = 'Date ' THEN STUFF(Name, 1, 5, '') ELSE Name END,
        Depth
FROM    Menus

SQL 小提琴上的拆分示例

正如旁注一样，您的表结构最终会得到冗余信息，可以通过计算递归次数来获得深度以返回到顶级父级，或者如果 marketID 存储在所有行中，则可以通过查找深度 = 0 来获得顶级父级。因此，上述查询的输出应该为您提供所需的一切。但我会继续说下去。

第一步是将所有项目插入菜单表中，0作为父项。

WITH Menus AS
(   SELECT  m.MarketID,
            [Name] = y.value('.', 'nvarchar(max)'),
            [Depth] = ROW_NUMBER() OVER(PARTITION BY MarketID ORDER BY y.value('for $i in . return count(../*[. << $i]) + 1', 'int')) - 1
    FROM    Markets m
             CROSS APPLY (VALUES (CAST('<x><y>' + REPLACE(menuPath, '', '</y><y>') + '</y><y>' + marketName + '</y></x>' AS XML))) a (x)
            CROSS APPLY x.nodes('/x/y') b (y)
    WHERE   y.value('.', 'nvarchar(max)') != ''
)
INSERT Menu (ParentID, Depth, Name, MarketID) 
SELECT  [ParentID] = 0,
        Depth,
        [Name] = CASE WHEN LEFT(Name, 5) = 'Date ' THEN STUFF(Name, 1, 5, '') ELSE Name END,
        MarketID
FROM    Menus

然后使用正确的父 ID 更新市场表

UPDATE  Menu
SET     ParentID = p.ID
FROM    Menu c
        INNER JOIN 
        (   SELECT  ID, MarketID, Depth
            FROM    Menu 
        ) p
            ON c.MarketID = p.MarketID
            AND c.Depth = p.Depth + 1

最后一步是将除基本菜单以外的所有菜单的市场 ID 设置为 null：

WITH CTE AS
(   SELECT  *,  
            [maxDepth] = MAX(Depth) OVER(PARTITION BY MarketID)
    FROM    Menu
)
UPDATE  CTE
SET     MarketID = NULL
WHERE   MaxDepth != Depth;

瞧，你有你想要的结果。

关于 SQL 小提琴的示例

---

补遗

这似乎有效：

CREATE TABLE #TempMenu (MarketID INT, Name VARCHAR(200) NOT NULL, Depth INT NOT NULL);
WITH Menus AS
(   SELECT  m.MarketID,
            [Name] = y.value('.', 'nvarchar(max)'),
            [Depth] = ROW_NUMBER() OVER(PARTITION BY MarketID ORDER BY y.value('for $i in . return count(../*[. << $i]) + 1', 'int')) - 1
    FROM    Markets m
             CROSS APPLY (VALUES (CAST('<x><y>' + REPLACE(menuPath, '', '</y><y>') + '</y><y>' + CASE WHEN SID = 3 THEN CONVERT(VARCHAR(5), [Time], 8) + ' ' ELSE '' END +  marketName + '</y></x>' AS XML))) a (x)
            CROSS APPLY x.nodes('/x/y') b (y)
    WHERE   y.value('.', 'nvarchar(max)') != ''
)
INSERT #TempMenu (MarketID, name, Depth)
SELECT  MarketID,
        [Name] = CASE WHEN LEFT(Name, 5) = 'Date ' THEN STUFF(Name, 1, 5, '') ELSE Name END,
        Depth
FROM    Menus;
CREATE TABLE #TempPaths 
(   ID          INT NOT NULL, 
    ParentID    INT NOT NULL, 
    Depth       INT NOT NULL, 
    Name        VARCHAR(200) NOT NULL, 
    MarketID    INT NULL, 
    ParentPath  VARCHAR(200) NULL, 
    CurrentPath VARCHAR(200) NULL
);
WITH Paths AS
(   SELECT  MarketID,
            [Name] = CASE WHEN LEFT(Name, 5) = 'Date ' THEN STUFF(Name, 1, 5, '') ELSE Name END,
            Depth,
            [MaxDepth] = MAX(Depth) OVER(PARTITION BY MarketID),
            [ParentPath] = (    SELECT  '/' + Name
                                FROM    #TempMenu p
                                WHERE   p.MarketID = c.MarketID
                                AND     p.Depth < c.Depth
                                FOR XML PATH(''), TYPE
                            ).value('.', 'nvarchar(max)'),
            [CurrentPath] = (   SELECT  '/' + Name
                                FROM    #TempMenu p
                                WHERE   p.MarketID = c.MarketID
                                AND     p.Depth <= c.Depth
                                FOR XML PATH(''), TYPE
                            ).value('.', 'nvarchar(max)')
    FROM    #TempMenu c
), Paths2 AS
(   SELECT  DISTINCT [ParentID] = 0, Depth, Name, [MarketID] = NULL, [ParentPath], [CurrentPath]
    FROM    Paths
    WHERE   MaxDepth != Depth
    UNION 
    SELECT  0, Depth, Name, MarketID, [ParentPath], [CurrentPath]
    FROM    Paths
    WHERE   MaxDepth = Depth
)
-- USE MERGE CONDITION THAT WILL NEVER MATCH, ALLOWS ACCESS TO VALUES NOT BEING INSERTED IN THE OUTPUT CLAUSE
MERGE INTO Menu m USING Paths2 p ON 1 = 0  
WHEN NOT MATCHED THEN
    INSERT (ParentID, Depth, Name, MarketID)
    VALUES (p.ParentID, p.Depth, p.Name, p.MarketID)
    OUTPUT inserted.ID, inserted.ParentID, inserted.Depth, inserted.Name, inserted.MarketID, p.ParentPath, p.CurrentPath INTO #TempPaths;
UPDATE  Menu
SET     ParentID = rel.ParentID
FROM    Menu
        INNER JOIN
        (   SELECT  [ChildID] = c.ID, [ParentID] = p.ID
            FROM    #TempPaths c
                    INNER JOIN #TempPaths p
                        ON c.ParentPath = p.CurrentPath
        ) rel
            ON rel.ChildID = Menu.ID;
DROP TABLE #TempMenu, #TempPaths;

为了大致解释发生了什么，我使用了与上面相同的方法将路径拆分为它们的组成部分，将它们放入临时表中（出于性能原因），然后将拆分的路径再次组合成完整路径并放入另一个临时表中，这些路径稍后将用于匹配父记录和子记录。

下一部分使用 merge 语句将记录插入菜单表，之所以使用此选项，是因为插入中的 Identity 需要与完整路径匹配，并且当使用 INSERT 时，OUTPUT仅允许访问插入的值，而不允许访问源中的其他列。

最后，一旦插入所有记录，路径的临时表可用于匹配父记录和子记录（基于路径），并更新菜单表。

这似乎是一个相当复杂的方法，但它完全是基于设置的，因此应该执行程序方法。