SQL Server 2005 中的树形数据处理示例-2

--=====================================================
--直接查询的应用实例
--=====================================================

--1.每个叶子结点的FullName

WITHstb([id],[FullName],[pid],[flag])
AS(
SELECT[id],CAST(RTRIM([name])asnvarchar(4000)),[pid],1
FROM[tb]A
WHERENOTEXISTS(
SELECT1FROM[tb]
WHERE[pid]=A.[id])
UNIONALL
SELECTA.[id],RTRIM(B.[name])+'/'+A.[FullName],B.[pid],A.flag+1
FROMstbA,[tb]B
WHEREA.[pid]=B.[id])
SELECT[id],[FullName]FROMstbA
WHERENOTEXISTS(
SELECT*FROMstb
WHERE[id]=A.[id]
ANDflag>A.flag)
ORDERBY[id]
GO

--2.每个结点的FullName

WITHstb([id],[FullName],[pid],[flag])
AS(
SELECT[id],CAST(RTRIM([name])asnvarchar(4000)),[pid],1
FROM[tb]
UNIONALL
SELECTA.[id],RTRIM(B.[name])+'/'+A.[FullName],B.[pid],A.flag+1
FROMstbA,[tb]B
WHEREA.[pid]=B.[id])
SELECT[id],[FullName]FROMstbA
WHERENOTEXISTS(
SELECT*FROMstb
WHERE[id]=A.[id]
ANDflag>A.flag)
ORDERBY[id]
GO

--3.树形显示数据

WITHstb([id],[level],[sid])
AS(
SELECT[id],1,CAST(RIGHT(10000+[id],4)asvarchar(8000))
FROM[tb]
WHERE[pid]=0
UNIONALL
SELECTA.[id],B.[level]+1,B.sid+RIGHT(10000+A.[id],4)
FROM[tb]A,stbB
WHEREA.[pid]=B.[id])
SELECTN'|'+REPLICATE('-',B.[level]*4)+A.name
FROM[tb]A,stbB
WHEREa.[id]=b.[id]
ORDERBYb.sid
GO

--4.检查不规范的数据

WITHchktb([id],[pid],[level],[Path],[Flag])
AS(
SELECT[id],[pid],1,
CAST([id]asvarchar(8000)),
CASEWHEN[id]=[pid]THEN1ELSE0END
FROM[tb]
UNIONALL
SELECTA.[id],B.[pid],B.[level]+1,
CAST(B.[Path]+'>'+RTRIM(A.[id])asvarchar(8000)),
CASEWHENA.[id]=B.[pid]THEN1ELSE0END
FROM[tb]A,chktbB
WHEREA.[pid]=B.[id]
ANDB.[Flag]=0)
SELECT*FROMchktb
WHERE[Flag]=1
ORDERBY[Path]
GO

--5.查询结点的所有子结点数

WITHsumtb([id],[level])
AS(
SELECT[pid],1
FROM[tb]A
WHERE[pid]<>0
UNIONALL
SELECTA.[pid],B.[level]+1
FROM[tb]A,sumtbB
WHEREA.[id]=B.[id]
ANDA.[pid]<>0)
SELECTA.[id],ChildCounts=COUNT(b.[id])
FROM[tb]A
LEFTJOINsumtbB
ONA.[id]=B.[id]
GROUPBYA.[id]
GO

--6.查询结点的所有父结点数

WITHsumtb([id],[level],[ParentCounts])
AS(
SELECT[id],1,0
FROM[tb]A
WHERE[pid]=0
UNIONALL
SELECTA.[id],B.[level]+1,B.[ParentCounts]+1
FROM[tb]A,sumtbB
WHEREA.[pid]=B.[id])
SELECT*FROMsumtb
orderby[ID]
GO