BOM之二----------------------------深度排序

--测试数据   深度排序    
  DECLARE   @t   TABLE(ID   char(3),PID   char(3),Name   nvarchar(10))  
  INSERT   @t   SELECT   '001',NULL   ,'山东省'  
  UNION   ALL   SELECT   '002','001','烟台市'  
  UNION   ALL   SELECT   '004','002','招远市'  
  UNION   ALL   SELECT   '003','001','青岛市'  
  UNION   ALL   SELECT   '005',NULL   ,'四会市'  
  UNION   ALL   SELECT   '006','005','清远市'  
  UNION   ALL   SELECT   '007','006','小分市'  
   
  --深度排序显示处理  
  --生成每个节点的编码累计(相同当单编号法的编码)  
  DECLARE   @t_Level   TABLE(ID   char(3),Level   int,Sort   varchar(8000))  
  DECLARE   @Level   int  
  SET   @Level=0  
  INSERT   @t_Level   SELECT   ID,@Level,ID  
  FROM   @t  
  WHERE   PID   IS   NULL  
  WHILE   @@ROWCOUNT>0  
  BEGIN  
  SET   @Level=@Level+1  
  INSERT   @t_Level   SELECT   a.ID,@Level,b.Sort+a.ID  
  FROM   @t   a,@t_Level   b  
  WHERE   a.PID=b.ID  
  AND   b.Level=@Level-1  
  END  
   
  --显示结果  
  SELECT   a.*  
  FROM   @t   a,@t_Level   b  
  WHERE   a.ID=b.ID  
  ORDER   BY   b.Sort  
  /*--结果  
  ID       PID       Name                
  ------   ---------   ----------    
  001     NULL   山东省  
  002     001       烟台市  
  004     002       招远市  
  003     001       青岛市  
  005     NULL   四会市  
  006     005       清远市  
  007     006       小分市  
  --*/

--查询指定节点及其所有子节点的函数  
  CREATE   FUNCTION   f_Cid(@ID   char(3))  
  RETURNS   @t_Level   TABLE(ID   char(3),Level   int)  
  AS  
  BEGIN  
  DECLARE   @Level   int  
  SET   @Level=1  
  INSERT   @t_Level   SELECT   @ID,@Level  
  WHILE   @@ROWCOUNT>0  
  BEGIN  
  SET   @Level=@Level+1  
  INSERT   @t_Level   SELECT   a.ID,@Level  
  FROM   tb   a,@t_Level   b  
  WHERE   a.PID=b.ID  
  AND   b.Level=@Level-1  
  END  
  RETURN  
  END  
  GO  
   
  --调用函数查询002及其所有子节点  
  SELECT   a.*  
  FROM   tb   a,f_Cid('002')   b  
  WHERE   a.ID=b.ID  
  /*--结果  
  ID       PID     Name                
  ------   -------   ----------    
  002     001     烟台市  
  004     002     招远市  
  --*/

 

--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'

--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
    SET @Level=@Level+1
    INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
    FROM @t a,@t_Level b
    WHERE a.PID=b.ID
        AND b.Level=@Level-1
END

--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/