• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

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

sqlserver 搞代码 3年前 (2018-06-16) 85次浏览 已收录 0个评论

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

— 1. 每个叶子结点的 FullName
WITH stb([id],[FullName],[pid],[flag])
AS(
    SELECT [id],CAST(RTRIM([name]) as nvarchar(4000)),[pid],1

http://www.gaodaima.com/36147.htmlSQL Server 2005 中的树形数据处理示例_sqlserver

    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


 

欢迎大家阅读《SQL Server 2005 中的树形数据处理示例_sqlserver,跪求各位点评,若觉得好的话请收藏本文,by 搞代码


喜欢 (0)
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址