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

  =====================================================  
  --   SQL   Server   2005   中的树形数据处理示例  
  --   作者:   邹建(引用请保留此信息)  
  --   2005.07  
  --   =====================================================  
   
  --   =====================================================  
  --   创建测试数据  
  --   =====================================================  
  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