有如下表 A:Id Parent_id
1 null
2 1
3 1
4 2
5 3请写出一个算法得到如下结果(顺序要一致)
Id Path
1 /
2 /2/
4 /2/4/
3 /3/
5 /3/5/
1 null
2 1
3 1
4 2
5 3请写出一个算法得到如下结果(顺序要一致)
Id Path
1 /
2 /2/
4 /2/4/
3 /3/
5 /3/5/
调试欢乐多
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-05-23 09:50:44
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Id] int,[Parent_id] int)
insert [tb]
select 1,null union all
select 2,1 union all
select 3,1 union all
select 4,2 union all
select 5,3
--------------开始查询--------------------------
;WITH T AS
(
SELECT *,CAST(ID AS VARCHAR(MAX)) AS PATH
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[Parent_id])
UNION ALL
SELECT A.*,CAST(B.PATH+'/'+CAST(A.ID AS VARCHAR(MAX)) AS VARCHAR(MAX))
FROM tb AS A
JOIN T AS B
ON A.[Parent_id]=B.id
)
SELECT Id,STUFF(PATH,1,1,'') FROM T ORDER BY PATH
----------------结果----------------------------
/* Id
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1
2 /2
4 /2/4
3 /3
5 /3/5(5 行受影响)
*/
INSERT INTO @tab
SELECT 1, NULL UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 2 UNION ALL
SELECT 5, 3;with cte AS
(
SELECT Id,[PATH]=CAST('/' AS VARCHAR),Parent_id FROM @tab WHERE Parent_id IS NULL
UNION ALL
SELECT a.Id,[PATH]=CAST(c.[PATH]+CAST(a.Id AS VARCHAR)+'/' AS VARCHAR),
a.Parent_id FROM @tab a JOIN cte c ON a.Parent_id=c.Id
)
SELECT id,PATH FROM cte ORDER BY id --test:
/*
id PATH
----------- ------------------------------
1 /
2 /2/
3 /3/
4 /2/4/
5 /3/5/(5 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-05-23 09:50:44
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Id] int,[Parent_id] int)
insert [tb]
select 1,null union all
select 2,1 union all
select 3,1 union all
select 4,2 union all
select 5,3
--------------开始查询--------------------------
;WITH T AS
(
SELECT *,CAST(ID AS VARCHAR(MAX)) AS PATH
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[Parent_id])
UNION ALL
SELECT A.*,CAST(B.PATH+'/'+CAST(A.ID AS VARCHAR(MAX)) AS VARCHAR(MAX))
FROM tb AS A
JOIN T AS B
ON A.[Parent_id]=B.id
)
SELECT Id,STUFF(PATH,1,1,'')+'/' AS PATH FROM T ORDER BY PATH----------------结果----------------------------
/* Id PATH
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 /
2 /2/
4 /2/4/
3 /3/
5 /3/5/(5 行受影响)
*/