BOM展开学习贴

-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-30 16:38:39
-------------------------------------

--> 生成测试数据: @T
DECLARE @T TABLE (id INT,pid INT,name VARCHAR(3))
INSERT INTO @T
SELECT 1,1,'A' UNION ALL
SELECT 2,2,'B' UNION ALL
SELECT 11,1,'AA' UNION ALL
SELECT 111,11,'AAA' UNION ALL
SELECT 22,2,'BB' UNION ALL
SELECT 33,2,'CC' UNION ALL
SELECT 44,11,'DDD' UNION ALL
SELECT 55,1,'EE'

--SQL查询如下:

;WITH Liang AS
(
SELECT *,CAST(ROW_NUMBER() OVER(ORDER BY id) AS VARBINARY(MAX)) AS [Path] ,
flag = CAST(id AS VARCHAR(MAX)),0 AS level FROM @T AS A
WHERE NOT EXISTS(SELECT * FROM @T WHERE id=A.pid AND id!=A.id)
UNION ALL
SELECT A.*,
CAST(B.[Path]+CAST(ROW_NUMBER() OVER(PARTITION BY B.pid ORDER BY A.id) AS BINARY(4))
AS VARBINARY(MAX)),
CAST(B.flag + '.' + RTRIM(A.id) AS VARCHAR(MAX)),level+1
FROM @T AS A
JOIN Liang AS B
ON A.pid=B.id
AND CHARINDEX('.'+RTRIM(A.id)+'.','.'+B.flag+'.')=0
)
SELECT REPLICATE('-',level)+name AS name FROM Liang ORDER BY [Path];

/*
name
---------------------------------
A
-AA
--DDD
--AAA
-EE
B
-BB
-CC

(8 行受影响)
*/

---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-30 17:19:15
---------------------------------
--> 生成测试数据表:tree

If not object_id('[tree]') is null
Drop table [tree]
Go
Create table [tree]([id] int,[pid] int,[name] nvarchar(3))
Insert tree
Select 1,1,'A' union all
Select 2,2,'B' union all
Select 11,1,'AA' union all
Select 111,11,'AAA' union all
Select 22,2,'BB' union all
Select 33,2,'CC' union all
Select 44,11,'DDD' union all
Select 55,1,'EE'
Go
--Select * from tree

-->SQL查询如下:
;with t as
(
select *,0 as lvl,px=cast(name as varchar(max))
from tree a
where not exists(
select 1
from tree
where case a.pid when a.id then 0 else a.pid end = id)
union all
select a.*,b.lvl+1,cast(b.px+'-'+a.name as varchar(max))
from tree a
join t b
on case a.pid when a.id then 0 else a.pid end=b.id
)
select replicate('-',lvl)+name as name
from t
order by px

/*
name
----------------
A
-AA
--AAA
--DDD
-EE
B
-BB
-CC

(8 行受影响)
*/