sql server: Parent/Child hierarchy tree view

sql server - Parent/Child hierarchy tree view - Stack Overflow

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148

--- declare @pc table(CHILD_ID int, PARENT_ID int, [NAME] varchar(80));    insert into @pc select 1,NULL,'Bill' union all select 2,1,'Jane' union all select 3,1,'Steve' union all select 4,2,'Ben' union all select 5,3,'Andrew' union all select 6,NULL,'Tom' union all select 7,8,'Dick' union all select 8,6,'Harry' union all select 9,3,'Stu' union all select 10,7,'Joe';       ; with r as (       select CHILD_ID, PARENT_ID, [NAME], depth=0, sort=cast(CHILD_ID as varchar(max))       from @pc       where PARENT_ID is null       union all       select pc.CHILD_ID, pc.PARENT_ID, pc.[NAME], depth=r.depth+1, sort=r.sort+cast(pc.CHILD_ID as varchar(30))       from r       inner join @pc pc on r.CHILD_ID=pc.PARENT_ID       where r.depth<32767 ) select tree=replicate('-',r.depth*3)+r.[NAME] from r order by sort option(maxrecursion 32767); GO       --https://www.sqlteam.com/articles/more-trees-hierarchies-in-sql     SELECT a.ID, a.CategoryName, COALESCE(b.CategoryName,'-') AS 'ParentName' FROM Category AS a LEFT JOIN Category AS b on a.ParentID = b.ID GO     create table pc ( parent varchar(10), child varchar(10) )   insert into pc values('a','b'); insert into pc values('a','c'); insert into pc values('b','e'); insert into pc values('b','f'); insert into pc values('a','d'); Insert into pc values('b','g'); insert into pc values('c','h'); insert into pc values('c','i'); insert into pc values('d','j'); insert into pc values('f','k'); insert into pc values('x','y'); insert into pc values('y','z'); insert into pc values('m','n');    DECLARE @parent varchar(10) = 'a';  WITH cte AS  (   select null parent, @parent child, 0 as level    union   SELECT  a.parent, a.child , 1 as level     FROM pc a    WHERE a.parent = @parent    UNION ALL   SELECT a.parent, a.child , c.level +    1   FROM pc a JOIN cte c ON a.parent = c.child   )   SELECT distinct parent, child , level   FROM cte   order by level, parent   go     -- https://stackoverflow.com/questions/9676637/sql-parent-child-tree-with-sort-order   -- https://stackoverflow.com/questions/49903446/sql-parent-child-tree-data-return-only-completed-tree-nodes-from-list   -- https://www.sqlteam.com/articles/more-trees-hierarchies-in-sql   declare @relations table(ParentID int, ChildID int, SortOrder int, treeID int);   insert into @relations values (0,1,0,0), (1,2,1,0), (2,3,2,0), (2,4,1,0), (2,6,3,0), (1,7,2,0), (1,9,3,0), (9,10,1,0), (9,12,2,0) --tree 0 , (0,1,0,1), (1,2,1,1), (2,3,2,1), (2,4,1,1), (2,6,3,1), (1,7,2,1), (1,9,3,1), (9,10,1,1), (9,12,2,1) --tree 1   ; with cte(ParentId,ChildId,SortOrder,depth,agg,treeID) as (     select null,ParentId,SortOrder,0     , right('0000000'+CAST(treeID as varchar(max)),7)         +right('0000000'+CAST(SortOrder as varchar(max)),7)     , treeID     from @relations where ParentId=0     union all     select cte.ChildId,r.ChildId,r.SortOrder,cte.depth+1     , cte.agg         +right('0000000'+CAST(r.treeID as varchar(max)),7)         +right('0000000'+CAST(r.SortOrder as varchar(max)),7)         +right('0000000'+CAST(r.ChildId as varchar(max)),7)     , r.treeID     from cte     inner join @relations r on r.ParentID=cte.ChildId     where cte.depth<32767     and r.treeID=cte.treeID ) select tree=case depth when 1 then cast(ParentID as varchar(30))+' (sort '+cast(SortOrder as varchar(30))+')'     else REPLICATE(CHAR(9),depth-1)         + cast(ChildId as varchar(30))+' (sort '+cast(SortOrder as varchar(30))+')'     end from cte where depth>0 order by agg option (maxrecursion 32767); go     ---顺序排列Geovin Du PageSet,CategoryIsDisplay,CategoryPojectId   WITH cte AS (   SELECT     ID,     [ParentID],     CategoryName,     PageSet,     IdOrder,     CAST(0 AS varbinary(max)) AS Level   FROM Category   WHERE [ParentID] is null and CategoryIsDisplay=1 and CategoryPojectId=1   UNION ALL   SELECT     i.ID,     i.[ParentID],     i.CategoryName,     i.PageSet,     i.IdOrder,      Level + CAST(i.ID AS varbinary(max)) AS Level   FROM Category i   INNER JOIN cte c     ON c.ID = i.[ParentID] ) SELECT   ID,   [ParentID],   CategoryName,   PageSet,   IdOrder FROM cte ORDER BY [Level]; go

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70

WITH cte AS (   SELECT     ID,     [ParentID],     CategoryName,     PageSet,     IdOrder,     dbo.f_getlevel(ID) AS Dulv,     CAST(0 AS varbinary(max)) AS Level    FROM Category   WHERE [ParentID] is null and CategoryIsDisplay=1 and CategoryPojectId=1   UNION ALL   SELECT     i.ID,     i.[ParentID],     i.CategoryName,     i.PageSet,     i.IdOrder,     dbo.f_getlevel(i.ID) AS Dulv,     Level + CAST(i.ID AS varbinary(max)) AS Level     FROM Category i   INNER JOIN cte c     ON c.ID = i.[ParentID] ) SELECT   ID,   [ParentID],   CategoryName,   PageSet,   IdOrder,   Dulv FROM cte ORDER BY [Level]; go   -- WITH cte AS (   SELECT     ID,     [ParentID],     CategoryName,     PageSet,     IdOrder,     dbo.f_getlevel(ID) AS Level   FROM Category   WHERE [ParentID] is null and CategoryIsDisplay=1 and CategoryPojectId=1   UNION ALL   SELECT     i.ID,     i.[ParentID],     i.CategoryName,     i.PageSet,     i.IdOrder,      dbo.f_getlevel(i.ID) AS Level   FROM Category i   INNER JOIN cte c     ON c.ID = i.[ParentID] ) SELECT   ID,   [ParentID],   CategoryName,   PageSet,   IdOrder,   Level FROM cte ORDER BY Level; go