使用 SQL CTE 打印树

2022-01-05 00:00:00 common-table-expression tree sql-server

架构如下:

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])

目前，我可以使用follow CTE获取所有逻辑数据，但我想以深度优先的方式直接打印它.

Currently, I can get all the logical data out with the follow CTE, but I would like to print it directly in a depth first fashion.

WITH SCTE (StructureId, Level, Seq, ParentId)
AS
(
  SELECT StructureId,  0, SequenceNumber, [ParentStructureId]
    FROM Structure
    WHERE [ParentStructureId] IS NULL 
          AND StructureId = 'F6C5F016-1270-47C1-972F-349C32DFC92A'

  UNION ALL

  SELECT Structure.StructureId, Level + 1, SequenceNumber, ParentStructureId
  FROM Structure
  INNER JOIN SCTE ON SCTE.StructureId = Structure.ParentStructureId
)

SELECT * FROM SCTE
ORDER BY Level, ParentId, Seq

输出如下(此处截断):

The output is as follows (truncated here):

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

如您所见，结果是广度优先"，这使得像现在这样打印树有点不可能.

As you can see, the result is 'breadth first' which makes printing a tree kinda impossible as it is now.

有什么方法(可能有一个简单的方法，但我的 SQL 技能非常差)以树打印友好"格式获取结果列表?

Is there any way (there probably is a trivial way, but my SQL skills are extremely poor) to get the resultant list in 'tree printing friendly' format?

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

I know I could just dump the results into a program and code the output, but as an exercise I would prefer doing this in SQL itself.

谢谢

推荐答案

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

Edited after comment. You could add the path to a node, and order on that:

declare @t table (id int, parent int)
insert @t (id, parent) values (1, null), (2,1), (3,2), (4,3), (5,null), (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) 转换中的数字.

This prints the root first, followed by leaves in order. If your id can be larger than 12 digits, increase the number in the char(x) casts.

相关文章