SQL Server 2005 中的树形数据处理示例-2
-- =====================================================-- 直接查询的应用实例
-- =====================================================
-- 1. 每个叶子结点的 FullName
WITH stb(,,,)
AS(
SELECT ,CAST(RTRIM() as nvarchar(4000)),,1
FROM A
WHERE NOT EXISTS(
SELECT 1 FROM
WHERE =A.)
UNION ALL
SELECT A.,RTRIM(B.)+'/'+A.,B.,A.flag+1
FROM stb A, B
WHERE A.=B.)
SELECT , FROM stb A
WHERE NOT EXISTS(
SELECT * FROM stb
WHERE =A.
AND flag>A.flag)
ORDER BY
GO
-- 2. 每个结点的 FullName
WITH stb(,,,)
AS(
SELECT ,CAST(RTRIM() as nvarchar(4000)),,1
FROM
UNION ALL
SELECT A.,RTRIM(B.)+'/'+A.,B.,A.flag+1
FROM stb A, B
WHERE A.=B.)
SELECT , FROM stb A
WHERE NOT EXISTS(
SELECT * FROM stb
WHERE =A.
AND flag>A.flag)
ORDER BY
GO
-- 3. 树形显示数据
WITH stb(,,)
AS(
SELECT ,1,CAST(RIGHT(10000+,4) as varchar(8000))
FROM
WHERE =0
UNION ALL
SELECT A.,B.+1,B.sid+RIGHT(10000+A.,4)
FROM A,stb B
WHERE A.=B.)
SELECT N'|'+REPLICATE('-',B.*4)+A.name
FROM A,stb B
WHERE a.=b.
ORDER BY b.sid
GO
-- 4. 检查不规范的数据
WITH chktb(,,,,)
AS(
SELECT ,,1,
CAST( as varchar(8000)),
CASE WHEN = THEN 1 ELSE 0 END
FROM
UNION ALL
SELECT A.,B.,B.+1,
CAST(B.+' > '+RTRIM(A.) as varchar(8000)),
CASE WHEN A.=B. THEN 1 ELSE 0 END
FROM A,chktb B
WHERE A.=B.
AND B.=0)
SELECT * FROM chktb
WHERE =1
ORDER BY
GO
-- 5. 查询结点的所有子结点数
WITH sumtb(,)
AS(
SELECT ,1
FROM A
WHERE <>0
UNION ALL
SELECT A.,B.+1
FROM A,sumtb B
WHERE A.=B.
AND A.<>0)
SELECT A.,ChildCounts=COUNT(b.)
FROM A
LEFT JOIN sumtb B
ON A.=B.
GROUP BY A.
GO
-- 6. 查询结点的所有父结点数
WITH sumtb(,,)
AS(
SELECT ,1,0
FROM A
WHERE =0
UNION ALL
SELECT A.,B.+1,B.+1
FROM A,sumtb B
WHERE A.=B.)
SELECT * FROM sumtb
order by
GO
页:
[1]