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

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

--创建测试数据

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[tb]')andOBJECTPROPERTY(id,N'IsUserTable')=1)
droptable[tb]
GO

--示例数据
createtable[tb]([id]intPRIMARYKEY,[pid]int,namenvarchar(20))
INSERT[tb]SELECT1,0,N'中国'
UNIONALLSELECT2,0,N'美国'
UNIONALLSELECT3,0,N'加拿大'
UNIONALLSELECT4,1,N'北京'
UNIONALLSELECT5,1,N'上海'
UNIONALLSELECT6,1,N'江苏'
UNIONALLSELECT7,6,N'苏州'
UNIONALLSELECT8,7,N'常熟'
UNIONALLSELECT9,6,N'南京'
UNIONALLSELECT10,6,N'无锡'
UNIONALLSELECT11,2,N'纽约'
UNIONALLSELECT12,2,N'旧金山'
GO

--查询指定id的所有子

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_cid]')andxtypein(N'FN',N'IF',N'TF'))
dropfunction[dbo].[f_cid]
GO
--=====================================================
--查询指定id的所有子
--邹建2005-07(引用请保留此信息)

--调用示例

/*--调用(查询所有的子)
SELECTA.*,层次=B.[level]
FROM[tb]A,f_cid(2)B
WHEREA.[id]=B.[id]
--*/
--=====================================================
CREATEFUNCTIONf_cid(@idint)
RETURNSTABLE
AS
RETURN(
WITHctb([id],[level])
AS(
SELECT[id],1FROM[tb]
WHERE[pid]=@id
UNIONALL
SELECTA.[id],B.[level]+1
FROM[tb]A,ctbB
WHEREA.[pid]=B.[id])
SELECT*FROMctb
--如果只显示最明细的子(下面没有子),则将上面这句改为下面的
--SELECT*FROMctbA
--WHERENOTEXISTS(
--SELECT1FROM[tb]WHERE[pid]=A.[id])
)
GO

--调用(查询所有的子)
SELECTA.*,层次=B.[level]
FROM[tb]A,f_cid(2)B
WHEREA.[id]=B.[id]
GO

--查询指定id的所有父

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_pid]')andxtypein(N'FN',N'IF',N'TF'))
dropfunction[dbo].[f_pid]
GO
--=====================================================
--查询指定id的所有父
--邹建2005-07(引用请保留此信息)

--调用示例

/*--调用(查询所有的父)
SELECTA.*,层次=B.[level]
FROM[tb]A,[f_pid](2)B
WHEREA.[id]=B.[id]
--*/
--=====================================================
CREATEFUNCTION[f_pid](@idint)
RETURNSTABLE
AS
RETURN(
WITHptb([id],[level])
AS(
SELECT[pid],1FROM[tb]
WHERE[id]=@id
AND[pid]<>0
UNIONALL
SELECTA.[pid],B.[level]+1
FROM[tb]A,ptbB
WHEREA.[id]=B.[id]
AND[pid]<>0)
SELECT*FROMptb
)
GO

--调用(查询所有的父)
SELECTA.*,层次=B.[level]
FROM[tb]A,[f_pid](7)B
WHEREA.[id]=B.[id]
GO

--树形分级显示

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_id]')andxtypein(N'FN',N'IF',N'TF'))
dropfunction[dbo].[f_id]
GO
--=====================================================
--级别及排序字段(树形分级显示)
--邹建2005-07(引用请保留此信息)

--调用示例

/*--调用实现树形显示

--调用函数实现分级显示
SELECTN'|'+REPLICATE('-',B.[level]*4)+A.name
FROM[tb]A,f_id()B
WHEREa.[id]=b.[id]
ORDERBYb.sid

--当然,这个也可以根本不用写函数,直接排序即可
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
--*/
--=====================================================
CREATEFUNCTIONf_id()
RETURNSTABLE
AS
RETURN(
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])
SELECT*FROMstb
)
GO

--调用函数实现分级显示
SELECTN'|'+REPLICATE('-',B.[level]*4)+A.name
FROM[tb]A,f_id()B
WHEREa.[id]=b.[id]
ORDERBYb.sid
GO