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

-- =====================================================
-- 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

-- =====================================================
-- 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. 树形显示数据

-- =====================================================
-- 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. 检查不规范的数据

-- =====================================================
-- 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. 查询结点的所有子结点数

-- =====================================================
-- 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. 查询结点的所有父结点数

-- =====================================================
-- 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

 

转载于:https://www.cnblogs.com/zhouchaoyi/articles/1782540.html