一个未解决的问题：SqlServer2005递归机构，如何做？

Question

本帖最后由 zhl1015 于 2010-04-14 15:49:37 编辑

Answer 1

with tb as(
select * from org where name='总部'
union all
select b.* from tb a
inner join org b on b.P_ID=a.id
)
select * from tb

Answer 2

CREATE TABLE ORG (
    ID varchar(50),
    NAME varchar(50),
    P_ID varchar(50)
)INSERT INTO ORG VALUES('1', '总部', '0')
INSERT INTO ORG VALUES('2', '子部1', '1')
INSERT INTO ORG VALUES('3', '子部2', '1')
INSERT INTO ORG VALUES('4', '子部2\1', '3')
INSERT INTO ORG VALUES('5', '子部1\1', '2')
INSERT INTO ORG VALUES('6', '子部3', '1')
INSERT INTO ORG VALUES('7', '子部1\1\1', '5');with f as(
select * from org where id=1
union all
select b.* from f a
inner join org b on b.P_ID=a.id
)
select name from fdrop table org/*name
--------------------------------------------------
总部
子部1
子部2
子部3
子部2\1
子部1\1
子部1\1\1(7 行受影响)*/

Answer 3

CREATE TABLE ORG (
    ID varchar(50),
    NAME varchar(50),
    P_ID varchar(50)
)INSERT INTO ORG VALUES('1', '总部', '0')
INSERT INTO ORG VALUES('2', '子部1', '1')
INSERT INTO ORG VALUES('3', '子部2', '1')
INSERT INTO ORG VALUES('4', '子部2\1', '3')
INSERT INTO ORG VALUES('5', '子部1\1', '2')
INSERT INTO ORG VALUES('6', '子部3', '1')
INSERT INTO ORG VALUES('7', '子部1\1\1', '5');with t as
(
select *,px=cast(right(1000+id,3) as varchar(1000)) from ORG where ID=1
union all
select a.*,cast(t.px+right(1000+a.id,3) as varchar(1000)) from ORG a join t on a.P_ID=t.ID
)
select name from t order by px
/*
name
--------------------------------------------------
总部
子部1
子部1\1
子部1\1\1
子部2
子部2\1
子部3(7 行受影响)*/

Answer 4

看明白了 是深度排序 参考:
--测试数据   深度排序     
  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
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/

Answer 5

2008里用hierarchyid很方便的。2005CTE循环。

Answer 6

谢谢你的回答，但是能不能不用ID这个字段，因为实际的情况，ID是乱的，而且子机构的ID也并不一定会比上级机构的ID大，不用ID能实现吗？

Answer 7

不好意思htl258 ，是我弄错了，试了一下，仍然可以实现，太感谢了！！！