SQL Server 2005 树形数据处理示例

 
-- ===================================================== 
-- SQL Server 2005 中的树形数据处理示例 
-- 作者: 邹建 

-- =====================================================

-- ===================================================== 
-- 创建测试数据 
-- ===================================================== 
if exists(select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table [tb] 
GO 
--示例数据 
create table [tb]([id] int PRIMARY KEY,[pid] int,name nvarchar(20)) 
INSERT [tb] SELECT 1,0,N'中国 ' 
UNION   ALL SELECT 2,0,N'美国 ' 
UNION   ALL SELECT 3,0,N'加拿大 ' 
UNION   ALL SELECT 4,1,N'北京 ' 
UNION   ALL SELECT 5,1,N'上海 ' 
UNION   ALL SELECT 6,1,N'江苏 ' 
UNION   ALL SELECT 7,6,N'苏州 ' 
UNION   ALL SELECT 8,7,N'常熟 ' 
UNION   ALL SELECT 9,6,N'南京 ' 
UNION   ALL SELECT 10,6,N'无锡 ' 
UNION   ALL SELECT 11,2,N'纽约 ' 
UNION   ALL SELECT 12,2,N'旧金山 ' 
GO 

 

-- =====================================================
-- 级别及排序字段(树形分级显示)
-- 直接查询的应用实例
-- =====================================================

 

-- ===================================================== 
-- 1. 每个叶子结点的 FullName 
-- ===================================================== 
WITH stb([id],[FullName],[pid],[flag]) 
AS( 
SELECT [id],CAST(RTRIM([name]) as nvarchar(4000)),[pid],1 
FROM [tb] A 
WHERE NOT EXISTS( 
SELECT 1 FROM [tb] 
WHERE [pid]=A.[id]) 
UNION ALL 
SELECT A.[id],RTRIM(B.[name])+ '/ '+A.[FullName],B.[pid],A.flag+1 
FROM stb A,[tb] B 
WHERE A.[pid]=B.[id]) 
SELECT [id],[FullName] FROM stb A 
WHERE NOT EXISTS( 
SELECT * FROM stb 
WHERE [id]=A.[id] 
AND flag> A.flag) 
ORDER BY [id] 
GO 

 

 

-- ===================================================== 
-- 2. 每个结点的 FullName 
-- ===================================================== 
WITH stb([id],[FullName],[pid],[flag]) 
AS( 
SELECT [id],CAST(RTRIM([name]) as nvarchar(4000)),[pid],1 
FROM [tb] 
UNION ALL 
SELECT A.[id],RTRIM(B.[name])+ '/ '+A.[FullName],B.[pid],A.flag+1 
FROM stb A,[tb] B 
WHERE A.[pid]=B.[id]) 
SELECT [id],[FullName] FROM stb A 
WHERE NOT EXISTS( 
SELECT * FROM stb 
WHERE [id]=A.[id] 
AND flag> A.flag) 
ORDER BY [id] 
GO 

 

 

-- ===================================================== 
-- 3. 树形显示数据 
-- ===================================================== 
WITH stb([id],[level],[sid]) 
AS( 
SELECT [id],1,CAST(RIGHT(10000+[id],4) as varchar(8000)) 
FROM [tb] 
WHERE [pid]=0 
UNION ALL 
SELECT A.[id],B.[level]+1,B.sid+RIGHT(10000+A.[id],4) 
FROM [tb] A,stb B 
WHERE A.[pid]=B.[id]) 
SELECT N'| '+REPLICATE( '- ',B.[level]*4)+A.name 
FROM [tb] A,stb B 
WHERE a.[id]=b.[id] 
ORDER BY b.sid 
GO 

 

 

-- ===================================================== 
-- 4. 检查不规范的数据 
-- ===================================================== 
WITH chktb([id],[pid],[level],[Path],[Flag]) 
AS( 
SELECT [id],[pid],1, 
CAST([id] as varchar(8000)), 
CASE WHEN [id]=[pid] THEN 1 ELSE 0 END 
FROM [tb] 
UNION ALL 
SELECT A.[id],B.[pid],B.[level]+1, 
CAST(B.[Path]+ ' > '+RTRIM(A.[id]) as varchar(8000)), 
CASE WHEN A.[id]=B.[pid] THEN 1 ELSE 0 END 
FROM [tb] A,chktb B 
WHERE A.[pid]=B.[id] 
AND B.[Flag]=0) 
SELECT * FROM chktb 
WHERE [Flag]=1 
ORDER BY [Path] 
GO 

 

 

-- ===================================================== 
-- 5. 查询结点的所有子结点数 
-- ===================================================== 
WITH sumtb([id],[level]) 
AS( 
SELECT [pid],1 
FROM [tb] A 
WHERE [pid] <> 0 
UNION ALL 
SELECT A.[pid],B.[level]+1 
FROM [tb] A,sumtb B 
WHERE A.[id]=B.[id] 
AND A.[pid] <> 0) 
SELECT A.[id],ChildCounts=COUNT(b.[id]) 
FROM [tb] A 
LEFT JOIN sumtb B 
ON A.[id]=B.[id] 
GROUP BY A.[id] 
GO 

 

-- ===================================================== 
-- 6. 查询结点的所有父结点数 
-- ===================================================== 
WITH sumtb([id],[level],[ParentCounts]) 
AS( 
SELECT [id],1,0 
FROM [tb] A 
WHERE [pid]=0 
UNION ALL 
SELECT A.[id],B.[level]+1,B.[ParentCounts]+1 
FROM [tb] A,sumtb B 
WHERE A.[pid]=B.[id]) 
SELECT * FROM sumtb 
order by [ID] 
GO