34,594
社区成员
发帖
与我相关
我的任务
分享
id pid level name rn
----------- ----------- ----------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16 15 0 c1 1
17 16 1 c2 1/1
18 17 2 c5 1/1/1
19 17 2 c3 1/1/2
id newName
----- ---------
16 c1
17 c1.c2
18 c1.c2.c5
19 c1.c2.c3
;WITH cte AS (
Select id,name,[level] from #T WHERE level=0
UNION ALL
SELECT #T.id ,
CONVERT(VARCHAR(200), cte.name + '.' + #T.name) ,
#T.level
FROM #T
JOIN cte ON cte.id = #T.pid
)
SELECT id,cte.name FROM cte
[/quote]谢谢版主,刚百度学习了一下。发现用递归查询的话确实很方便,不过也有个缺陷就是必须指定根节点,如果根节点的数量很多的话就比较麻烦了。不过这个递归查询确实方便了。谢谢版主,学习了;WITH cte AS (
Select id,name,[level] from #T WHERE level=0
UNION ALL
SELECT #T.id ,
CONVERT(VARCHAR(200), cte.name + '.' + #T.name) ,
#T.level
FROM #T
JOIN cte ON cte.id = #T.pid
)
SELECT id,cte.name FROM cte
SELECT '16','15','0','c1' UNION ALL
select '17','16','1','c2' UNION ALL
select '18','17','5','c5' UNION ALL
select '19','17','5','c3' UNION ALL
select '20','19','9','c9' UNION ALL
select '21','20','10','c11'
--出来的结果就是这样的
id name
-------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16 c1
17 c1.c2
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab(
id VARCHAR(20),
pid VARCHAR(20),
[LEVEL] VARCHAR(20),
NAME VARCHAR(200)
)
INSERT INTO #tab
SELECT '16','15','0','c1' UNION ALL
select '17','16','1','c2' UNION ALL
select '18','17','2','c5' UNION ALL
select '19','17','2','c3' UNION ALL
select '20','19','3','c9' UNION ALL
select '21','20','4','c11'
--测试数据结束
--从下面的代码你可以看出,如果你的数据是N级,
--就要left outer join N-1个表,同时在select的时候把表的name拼接起来
--对比上面的3级关系的就多了那么些代码,应该很容易看懂N级该怎么写吧
SELECT a.id,
--新增代码
CASE
WHEN ISNULL(e.name, '') = '' THEN ''
ELSE e.name + '.'
END +
CASE
WHEN ISNULL(d.name, '') = '' THEN ''
ELSE d.name + '.'
END +
--新增代码结束
CASE
WHEN ISNULL(c.name, '') = '' THEN ''
ELSE c.name + '.'
END +
CASE
WHEN ISNULL(b.name, '') = '' THEN ''
ELSE b.name + '.'
END + a.name AS [newname]
FROM #tab a
LEFT OUTER JOIN #tab b
ON a.pid = b.id
LEFT OUTER JOIN #tab c
ON b.pid = c.id
--新增代码
LEFT OUTER JOIN #tab d
ON c.pid = d.id
LEFT OUTER JOIN #tab e
ON d.pid = e.id
--新增代码结束
id newname
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16 c1
17 c1.c2
18 c1.c2.c5
19 c1.c2.c3
20 c1.c2.c3.c9
21 c1.c2.c3.c9.c11
(6 行受影响)
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T
(id VARCHAR(20),
pid VARCHAR(20),
[LEVEL] VARCHAR(20),
NAME VARCHAR(200)
)
Insert #T
SELECT '16','15','0','c1' UNION ALL
select '17','16','1','c2' UNION ALL
select '18','17','2','c5' UNION ALL
select '19','17','2','c3' UNION ALL
select '20','19','3','c9' UNION ALL
select '21','20','4','c11'
Go
--测试数据结束
;WITH cte AS (
Select id,name,[level] from #T WHERE level=0
UNION ALL
SELECT #T.id ,
CONVERT(VARCHAR(200), cte.name + '.' + #T.name) ,
#T.level
FROM #T
JOIN cte ON cte.id = #T.pid
WHERE cte.level + 1 = #T.level
)
SELECT id,cte.name FROM cte
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab(
id VARCHAR(20),
pid VARCHAR(20),
[LEVEL] VARCHAR(20),
NAME VARCHAR(200)
)
INSERT INTO #tab
SELECT '16','15','0','c1' UNION ALL
select '17','16','1','c2' UNION ALL
select '18','17','2','c5' UNION ALL
select '19','17','2','c3' UNION ALL
select '20','19','3','c9' UNION ALL
select '21','20','4','c11'
--测试数据结束
SELECT a.id,
CASE
WHEN ISNULL(c.name, '') = '' THEN ''
ELSE c.name + '.'
END +
CASE
WHEN ISNULL(b.name, '') = '' THEN ''
ELSE b.name + '.'
END + a.name AS [newname]
FROM #tab a
LEFT OUTER JOIN #tab b
ON a.pid = b.id
LEFT OUTER JOIN #tab c
ON b.pid = c.id
得出的结果是
id newname
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16 c1
17 c1.c2
18 c1.c2.c5
19 c1.c2.c3
20 c2.c3.c9
21 c3.c9.c11
id为20想要的数据是c1.c2.c3.c9
id为21想要的数据是c1.c2.c3.c9.c11
这个问题怎么破?非常感谢"@听雨停了"
--测试数据
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab(
id VARCHAR(20),
pid VARCHAR(20),
[LEVEL] VARCHAR(20),
NAME VARCHAR(200)
)
INSERT INTO #tab
SELECT '16','15','0','c1' UNION ALL
select '17','16','1','c2' UNION ALL
select '18','17','2','c5' UNION ALL
select '19','17','2','c3'
--测试数据结束
SELECT a.id,
CASE
WHEN ISNULL(c.name, '') = '' THEN ''
ELSE c.name + '.'
END +
CASE
WHEN ISNULL(b.name, '') = '' THEN ''
ELSE b.name + '.'
END + a.name AS [newname]
FROM #tab a
LEFT OUTER JOIN #tab b
ON a.pid = b.id
LEFT OUTER JOIN #tab c
ON b.pid = c.id
id newname
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16 c1
17 c1.c2
18 c1.c2.c5
19 c1.c2.c3
(4 行受影响)