sql With As 递归实现树形结构数据初始化

今天遇到一个问题记录下，我们需要导入其他系统的树形结构数据，但是我们这边的层级是要以 父级.子级.子级 这样的Code 才能实现，而对方给到的数据却是以当前编码和父级编码这样的数据给到我们，所以只能对编码做点改造，话不多说，直接上图，下面为待整理的数据

--表结构和数据
CREATE TABLE [dbo].[s_TreeDemo](
	[CODE] [varchar](255) NULL,
	[DISPLAYORDER] [varchar](255) NULL,
	[PARENTID] [char](12) NULL,
	[NAME] [varchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P0000', N'000000021348', N'0           ', N'教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8300', N'000000021349', N'000000021348', N'教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8310', N'000000021350', N'000000021349', N'学前教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8320', N'000000021351', N'000000021349', N'初等教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8321', N'000000021352', N'000000021351', N'普通小学教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8322', N'000000021353', N'000000021351', N'成人小学教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8330', N'000000021354', N'000000021349', N'中等教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8331', N'000000021355', N'000000021354', N'普通初中教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8332', N'000000021356', N'000000021354', N'职业初中教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8333', N'000000021357', N'000000021354', N'成人初中教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8334', N'000000021358', N'000000021354', N'普通高中教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8335', N'000000021359', N'000000021354', N'成人高中教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8336', N'000000021360', N'000000021354', N'中等职业学校教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8340', N'000000021361', N'000000021349', N'高等教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8341', N'000000021362', N'000000021361', N'普通高等教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8342', N'000000021363', N'000000021361', N'成人高等教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8350', N'000000021364', N'000000021349', N'特殊教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8390', N'000000021365', N'000000021349', N'技能培训、教育辅助及其他教育')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8391', N'000000021366', N'000000021365', N'职业技能培训')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8392', N'000000021367', N'000000021365', N'体校及体育培训')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8393', N'000000021368', N'000000021365', N'文化艺术培训')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8394', N'000000021369', N'000000021365', N'教育辅助服务')
INSERT [dbo].[s_TreeDemo] ([CODE], [DISPLAYORDER], [PARENTID], [NAME]) VALUES (N'P8399', N'000000021370', N'000000021365', N'其他未列明教育')

数据插入后待处理的显示效果

分析：只要是树形结构的一般都会有这两个列，一个是当前节点Code 一个是父级节点Code，我们只需要递归去刷一下Code和名称就可以实现，代码如下。

WITH child (CODE, NAME, DISPLAYORDER, PARENTID, level, newCode, newName, parentCode, IfEnd)
	AS (
		SELECT
				CODE,
				NAME,
				DISPLAYORDER,
				PARENTID,
				1 AS level,
				CONVERT (VARCHAR(100), CODE) AS newCode,
				CONVERT (VARCHAR(300), NAME) AS newName,
				CODE AS parentCode,
				0 AS IfEnd
		FROM	s_TreeDemo
		WHERE DISPLAYORDER = '000000021348' --最顶级编码
		UNION ALL
		SELECT
				a.CODE,
				a.NAME,
				a.DISPLAYORDER,
				a.PARENTID,
				b.level + 1,
				CONVERT (VARCHAR(100), b.newCode + '.' + a.CODE) AS newCode,
				CONVERT (VARCHAR(300), b.newName + '-' + a.NAME) AS newName,
				b.CODE AS parentCode,
				IfEnd = (CASE
								WHEN	EXISTS
										(	SELECT
															1
												FROM		dbo.s_TreeDemo
												WHERE		PARENTID = a.DISPLAYORDER) THEN
									0
								ELSE 1
							END)
		FROM	dbo.s_TreeDemo a
				INNER JOIN child b ON a.PARENTID = b.DISPLAYORDER
		)
SELECT
			*
FROM		child
ORDER BY child.CODE;

结果

查询当前所有父级节点

SELECT
		*
FROM	s_TreeDemo A
WHERE NOT EXISTS
	(	SELECT
				*
		FROM	s_TreeDemo B
		WHERE A.PARENTID = B.DISPLAYORDER);

查询当前所有末级节点

SELECT
		*
FROM	s_TreeDemo A
WHERE NOT EXISTS
	(	SELECT
				*
		FROM	s_TreeDemo B
		WHERE A.DISPLAYORDER = B.PARENTID);