使用 SQL CTE 打印树

本文介绍了使用 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])

目前，我可以使用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.