分享
为什么问答平台  ›  专栏  ›  技术社区  ›  Anuj Tamrakar

如何为下面的层次结构表动态分配代码?[副本] - How do you dynamically Assing Code for the following hierarhical table? [duplicate]

  •  0
  • Anuj Tamrakar  · 技术社区  · 1 周前

    Id  ParentId Name   Code
    1   Null     John  
    2   1        Harry
    3   1        Mary
    4   2        Emma
    5   2        Kyle
    6   4        Robert
    7   Null     Rohit
    

    我想为每个人分配以下格式的唯一层次结构代码

     Id   ParentId   Name      Code
        1   Null     John     1
        2   1        Harry    1.1
        3   1        Mary     1.2
        4   2        Emma     1.1.1
        5   2        Kyle     1.1.2
        6   4        Robert   1.1.1.1
        7  Null      Rohit    2
    

    1 回复  |  直到 1 周前
        1
  •  0
  •   Shnugo    1 周前

    您可以将递归CTE与 ROW_NUMBER()

    DECLARE @dummy TABLE(Id INT,ParentId INT,[Name] VARCHAR(100));
    INSERT INTO @dummy(Id,ParentId,[Name]) VALUES
     (1,Null,'John')  
    ,(2,1   ,'Harry')
    ,(3,1   ,'Mary')
    ,(4,2   ,'Emma')
    ,(5,2   ,'Kyle')
    ,(6,4   ,'Robert')
    ,(7,Null,'Rohit');
    
    WITH recCTE AS
    (
        SELECT Id,ParentId,[Name]
              ,CONCAT(N'.',CAST(ROW_NUMBER() OVER(ORDER BY Id) AS NVARCHAR(MAX))) AS Code 
        FROM @dummy WHERE ParentId IS NULL
    
        UNION ALL
        SELECT d.Id,d.ParentId,d.[Name]
              ,CONCAT(r.Code,N'.', ROW_NUMBER() OVER(ORDER BY d.Id)) 
        FROM @dummy d
        INNER JOIN recCTE r ON d.ParentId=r.Id
    )
    SELECT Id,ParentId,[Name] 
          ,STUFF(Code,1,1,'') AS Code
    FROM RecCTE;
    

    我们用 ParentId IS NULL


    决赛 SELECT STUFF 为了去掉第一个点。

    WITH recCTE AS
    (
        SELECT Id,ParentId,[Name]
              ,CONCAT(N'.',CAST(ROW_NUMBER() OVER(ORDER BY Id) AS NVARCHAR(MAX))) AS Code 
              ,CONCAT(N'000',CAST(ROW_NUMBER() OVER(ORDER BY Id) AS NVARCHAR(MAX))) AS Code2 
        FROM @dummy WHERE ParentId IS NULL
    
        UNION ALL
        SELECT d.Id,d.ParentId,d.[Name]
              ,CONCAT(r.Code,N'.', ROW_NUMBER() OVER(ORDER BY d.Id)) 
              ,CONCAT(r.Code2,RIGHT(CONCAT('0000',ROW_NUMBER() OVER(ORDER BY d.Id)),4))
        FROM @dummy d
        INNER JOIN recCTE r ON d.ParentId=r.Id
    )
    SELECT Id,ParentId,[Name] 
          ,STUFF(Code,1,1,'') AS Code
          ,Code2
    FROM RecCTE
    ORDER BY Code2;