ITPub博客

首页 > 应用开发 > IT综合 > if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUse

if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUse

原创 IT综合 作者:caixia615 时间:2007-01-23 12:24:58 0 删除 编辑

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

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

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8591181/viewspace-892703/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    1255
  • 访问量
    57685637