SQL Server 2005 中的树形数据处理示例 (转载)

--   =====================================================  
  --   SQL   Server   2005   中的树形数据处理示例  
  --   作者:   邹建(引用请保留此信息)   
  --   =====================================================  
   
  --   =====================================================  
  --   创建测试数据  
  --   =====================================================  
  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  
   
  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  
   
   
  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  
   
   
  --   =====================================================  
  --   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,A.[id]  
  FROM   [tb]   A,stb   B    
  WHERE   a.[id]=b.[id]  
  ORDER   BY   b.sid  
  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