sql递归查找子类型

编程有钱人了 2009-04-20 03:00:46

A表

parentid name childid
1 hah 0
2 huhu 0
3 heihei 0
4 sss 1
5 eeee 1
6 pppp 2
7 ttttt 2
8 uuuuu 4
9 rr 5
...
.....

这个是个递归表

B表
id name pid ----这里的pid和A表的parentid 关联
1 方法 6 ---这个6是上一个表pppp字段的再往上就是pppp的上一级huhu

...
.
.

我想先取hah 或huhu 的所有子类型这样在B表就可以 SELECT * from B where pid in （A表查询的hah的左右子类）

我是这样做的



SELECT PARENTID  from A   WHERE CHILDID in  (SELECT PARENTID  from A   WHERE CHILDID=1) union  SELECT PARENTID  from A   WHERE CHILDID=1

但是这样只能取2次递归

但是下面可能递归号几次

我这么才能把 hah下面属于他的子类全取出来？

以便
在B表就可以 SELECT * from B where pid in （A表查询的hah的左右子类）

...全文

251 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

让你望见影子的墙 2009-04-21

打赏
举报

2005还可以这么用：

USE tempdb

GO



-- 建立演示环境

CREATE TABLE Dept(

	id int PRIMARY KEY, 

	parent_id int,

	name nvarchar(20))

INSERT Dept

SELECT 0, 0, N'<全部>' UNION ALL

SELECT 1, 0, N'财务部' UNION ALL

SELECT 2, 0, N'行政部' UNION ALL

SELECT 3, 0, N'业务部' UNION ALL

SELECT 4, 0, N'业务部' UNION ALL

SELECT 5, 4, N'销售部' UNION ALL

SELECT 6, 4, N'MIS' UNION ALL

SELECT 7, 6, N'UI' UNION ALL

SELECT 8, 6, N'软件开发' UNION ALL

SELECT 9, 8, N'内部开发'

GO



-- 查询指定部门下面的所有部门

DECLARE @Dept_name nvarchar(20)

SET @Dept_name = N'MIS'

;WITH

DEPTS AS(

	-- 定位点成员

	SELECT * FROM Dept

	WHERE name = @Dept_name

	UNION ALL

	-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归

	SELECT A.*

	FROM Dept A, DEPTS B

	WHERE A.parent_id = B.id

)

SELECT * FROM DEPTS

GO



-- 删除演示环境

DROP TABLE Dept

编程有钱人了 2009-04-21

打赏
举报

[Quote=引用 5 楼 ws_hgo 的回复:]
引用 3 楼 wangjun8868 的回复:
LS2位太强大了吧

真谢谢你们

论坛里面收
BOM数
成千上万的帖子
你把上面的搞懂啦
自然就会拉
[/Quote]

好人

ws_hgo 2009-04-20

打赏
举报

[Quote=引用 3 楼 wangjun8868 的回复:]
LS2位太强大了吧

真谢谢你们
[/Quote]
论坛里面收
BOM数
成千上万的帖子
你把上面的搞懂啦
自然就会拉

编程有钱人了 2009-04-20

打赏
举报

告诉我这么再实际引用使用？刚才试le 不行

编程有钱人了 2009-04-20

打赏
举报

LS2位太强大了吧

真谢谢你们

htl258_Tony 2009-04-20

打赏
举报

引用：   

  --测试数据   深度排序     

  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

/*--结果

|--山东省

  |--烟台市

    |--招远市

  |--青岛市

|--四会市

  |--清远市

    |--小分市

--*/

BOM展开算法.

csdyyr 2009-04-20

打赏
举报

 --测试数据   深度排序     

  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

/*--结果

|--山东省

  |--烟台市

    |--招远市

  |--青岛市

|--四会市

  |--清远市

    |--小分市

--*/