-- ===================================================== -- 直接查询的应用实例 -- ===================================================== -- 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
[1] [2] 下一页 [Access]sql随机抽取记录 [Access]ASP&SQL让select查询结果随机排序的实现方法 [Web开发]VS2005发布网站与生成网站的区别 [聊天工具]新浪UC2005III正式版发布 [聊天工具]新浪UC2005III Beta2新鲜试用__天极Yesky [聊天工具]新浪UC2005III Beta2新功能介绍__天极Yesky [聊天工具]UC2005III Beta2发布 主界面重新设计__天极Yesky [聊天工具]新浪UC2005III Beta1新功能介绍__天极Yesky [聊天工具]最精简的QQ2005:只用最核心的几个文件 [聊天工具]腾讯QQ2005 Beta3珊瑚虫版 v3.2发布__天极Yesky
|