sql-server – 使用SQL CTE打印树

架构如下：

CREATE TABLE [Structure](
    [StructureId] [uniqueidentifier] NOT NULL,[SequenceNumber] [int] NOT NULL,-- order for siblings,unique per parent
    [ParentStructureId] [uniqueidentifier] NULL,CONSTRAINT [Structure_PK] PRIMARY KEY CLUSTERED 
(
    [StructureId] ASC
)
) ON [PRIMARY]
 
ALTER TABLE [Structure]  WITH CHECK ADD  CONSTRAINT [Structure_FK1] 
FOREIGN KEY([ParentStructureId])
REFERENCES [Structure] ([StructureId])

目前,我可以通过以下CTE获取所有逻辑数据,但我想以深度优先的方式直接打印它.

WITH SCTE (StructureId,Level,Seq,ParentId)
AS
(
  SELECT StructureId,SequenceNumber,[ParentStructureId]
    FROM Structure
    WHERE [ParentStructureId] IS NULL 
          AND StructureId = 'F6C5F016-1270-47C1-972F-349C32DFC92A'
 
  UNION ALL
 
  SELECT Structure.StructureId,Level + 1,ParentStructureId
  FROM Structure
  INNER JOIN SCTE ON SCTE.StructureId = Structure.ParentStructureId
)
 
SELECT * FROM SCTE
ORDER BY Level,ParentId,Seq

输出如下(此处截断)：

StructureId                     Level   Seq ParentId
F6C5F016-1270-47C1-972F-349C32DFC92A    0   0   NULL
D2E34429-401A-4A49-9E18-E81CCA0FB417    1   0   F6C5F016-1270-47C1-972F-349C32DFC92A
0CC5E16C-9194-40CA-9F72-1CED2972D7CA    1   1   F6C5F016-1270-47C1-972F-349C32DFC92A
1ECD1D30-EB85-42B0-969F-75794343E3B4    1   2   F6C5F016-1270-47C1-972F-349C32DFC92A
EEC3A981-B790-4600-8CD1-F15972CD9230    2   0   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
4406F639-2F58-4918-A9EF-A4B0F379BEA0    2   1   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
FCAF7870-C606-4AA6-85EE-57B90B1B0CC3    2   2   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
855DF5FB-1593-4E5B-8EF9-3770B45F89D6    2   3   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
3D16DF32-C04F-49B4-B0D9-5BDC9104F810    2   4   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
A1084D00-0198-47D9-87E0-BB8234233F14    2   5   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
CE443C0D-376F-46EC-9914-32C6B7200DB1    2   6   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
0DEA587D-4FCF-414C-AD71-FB00829F8082    2   7   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
CC9FC8D3-254A-486B-8DC4-07E57627476C    2   0   1ECD1D30-EB85-42B0-969F-75794343E3B4
215565CC-501F-4850-B8AE-5466DA5E6854    2   1   1ECD1D30-EB85-42B0-969F-75794343E3B4
D4E6C8E5-5ADD-4AD1-B59B-1A672F66888A    2   2   1ECD1D30-EB85-42B0-969F-75794343E3B4
796C65BF-4714-4DBF-A97A-2150DBE3098C    2   3   1ECD1D30-EB85-42B0-969F-75794343E3B4
B39DEB9C-BE42-43B4-9C38-968399D7D1E2    2   4   1ECD1D30-EB85-42B0-969F-75794343E3B4
6C2F70C6-1DA0-4E1A-BBC1-D7FCAFE6AFEE    2   0   D2E34429-401A-4A49-9E18-E81CCA0FB417
75D7B43B-C971-46B4-BC42-58C3605ADD79    2   1   D2E34429-401A-4A49-9E18-E81CCA0FB417
0B5AAAA0-A69F-431E-86BA-148444D7B1E6    2   2   D2E34429-401A-4A49-9E18-E81CCA0FB417
CB3CF66B-D83A-45E2-953A-6F0CEE094F5B    2   3   D2E34429-401A-4A49-9E18-E81CCA0FB417
1D5F69C3-F036-4667-BD75-A0DC1506DB6D    2   4   D2E34429-401A-4A49-9E18-E81CCA0FB417
71B894F7-B9FC-44DE-AEDB-E6FA026A6082    2   5   D2E34429-401A-4A49-9E18-E81CCA0FB417
F1DFA1E1-013B-449C-9D9D-14C64E75D418    2   6   D2E34429-401A-4A49-9E18-E81CCA0FB417

正如您所看到的,结果是“广度优先”,这使得打印树像现在一样不可能.

是否有任何方法(可能有一种微不足道的方式,但我的sql技能非常差)以“树形打印友好”格式获得结果列表？

我知道我可以将结果转储到程序中并对输出进行编码,但作为练习,我更喜欢在sql本身中执行此操作.

谢谢

解决方法

评论后编辑.您可以将路径添加到节点,并对其进行排序：

declare @t table (id int,parent int)
insert @t (id,parent) values (1,null),(2,1),(3,2),(4,3),(5,(6,5)
 
; with cte as (
    select  id,parent,cast(RIGHT(REPLICATE('0',12) + 
                 CONVERT(varchar(12),id),12) as varchar(max)) Path
    from    @t
    where   parent is null
    union all
    select  child.id,child.parent,parent.Path + RIGHT(REPLICATE('0',12) + 
                                CONVERT(varchar(12),child.id),12) as Path
    from    @t child
    join    cte parent
    on      parent.id = child.parent
)
select  *
from    cte
order by
        Path

这将首先打印根,然后按顺序打印.如果您的id可能大于12位,请增加char(x)强制转换中的数字.

sql-server – 使用SQL CTE打印树

解决方法

猜你在找的MsSQL相关文章