-- 创建测试数据 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 -- 查询指定id的所有子 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cid]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_cid] GO -- ===================================================== -- 查询指定id的所有子 -- 邹建 2005-07(引用请保留此信息) -- 调用示例 /*--调用(查询所有的子) SELECT A.*,层次=B.[level] FROM [tb] A,f_cid(2)B WHERE A.[id]=B.[id] --*/ -- ===================================================== CREATE FUNCTION f_cid(@id int) RETURNS TABLE AS RETURN( WITH ctb([id],[level]) AS( SELECT [id],1 FROM [tb] WHERE [pid]=@id UNION ALL SELECT A.[id],B.[level]+1 FROM [tb] A,ctb B WHERE A.[pid]=B.[id]) SELECT * FROM ctb --如果只显示最明细的子(下面没有子),则将上面这句改为下面的 -- SELECT * FROM ctb A -- WHERE NOT EXISTS( -- SELECT 1 FROM [tb] WHERE [pid]=A.[id]) ) GO --调用(查询所有的子) SELECT A.*,层次=B.[level] FROM [tb] A,f_cid(2)B WHERE A.[id]=B.[id] GO -- 查询指定id的所有父 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_pid]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_pid] GO -- ===================================================== -- 查询指定id的所有父 -- 邹建 2005-07(引用请保留此信息) -- 调用示例 /*--调用(查询所有的父) SELECT A.*,层次=B.[level] FROM [tb] A,[f_pid](2)B WHERE A.[id]=B.[id] --*/ -- ===================================================== CREATE FUNCTION [f_pid](@id int) RETURNS TABLE AS RETURN( WITH ptb([id],[level]) AS( SELECT [pid],1 FROM [tb] WHERE [id]=@id AND [pid]<>0 UNION ALL SELECT A.[pid],B.[level]+1 FROM [tb] A,ptb B WHERE A.[id]=B.[id] AND [pid]<>0) SELECT * FROM ptb ) GO --调用(查询所有的父) SELECT A.*,层次=B.[level] FROM [tb] A,[f_pid](7)B WHERE A.[id]=B.[id] GO -- 树形分级显示 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_id] GO -- ===================================================== -- 级别及排序字段(树形分级显示) -- 邹建 2005-07(引用请保留此信息) -- 调用示例 /*--调用实现树形显示 --调用函数实现分级显示 SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name FROM [tb] A,f_id()B WHERE a.[id]=b.[id] ORDER BY b.sid --当然,这个也可以根本不用写函数,直接排序即可 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 --*/ -- ===================================================== CREATE FUNCTION f_id() RETURNS TABLE AS RETURN( 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 * FROM stb ) GO --调用函数实现分级显示 SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name FROM [tb] A,f_id()B WHERE a.[id]=b.[id] ORDER BY b.sid 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 |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-29 17:22 , Processed in 0.247204 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.