SQL每日一题(20210315)

题目

有如下一张表T0311

希望得到如下结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CVDTQWUh-1615905813416)(./images/结果-每日一题20210315.png)]

即根据父ID（PARENTID）来逐级显示产品名和层级序号

测试数据

CREATE TABLE T0315(
   ID INT,
   PRODUCTNAME VARCHAR(64),
   PARENTID INT,
)
 
INSERT INTO T0315 VALUES ( 1,'汽车',NULL)
INSERT INTO T0315 VALUES ( 2,'车身',1)
INSERT INTO T0315 VALUES ( 3,'发动机',1)
INSERT INTO T0315 VALUES ( 4,'车门',2)
INSERT INTO T0315 VALUES ( 5,'驾驶舱',2)
INSERT INTO T0315 VALUES ( 6,'行李舱',2)
INSERT INTO T0315 VALUES ( 7,'气缸',3)
INSERT INTO T0315 VALUES ( 8,'活塞',3)

参考答案

官方答案:

;WITH CTE AS
(
	SELECT ID,PARENTID,PRODUCTNAME,0 CTELEVEL,CAST(ID AS VARCHAR) ORDERID
	FROM T0315
	WHERE ID=1
	UNION ALL
	SELECT
	A.ID,A.PARENTID,A.PRODUCTNAME,B.CTELEVEL+1,CAST(B.ORDERID+'->'+LTRIM(A.ID) AS VARCHAR)
	FROM T0315 A
	INNER JOIN CTE B ON B.ID=A.PARENTID
)
SELECT ID,PARENTID,
RIGHT('        ',4*CTELEVEL)+PRODUCTNAME PRODUCTNAME,ORDERID
FROM CTE
ORDER BY ORDERID

考点: WITH CTE AS(XX UNION ALL CTE)、RIGHT()、LTRIM()

说明:在with块中继续使用CTE变相使用递归思想

参考:SQL每日一题(20210315)