SQL 递归查询

最新推荐文章于 2025-01-13 17:48:39 发布

weixin_33816611

最新推荐文章于 2025-01-13 17:48:39 发布

阅读量50

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/pato/archive/2012/03/31/2427210.html

CREATE TABLE [ptable](
[id] [int] NULL,
[pid] [int] NULL,
[name] [nchar](10)
)
GO
INSERT INTO ptable VALUES(1,0,'a')
INSERT INTO ptable VALUES(2,0,'b')
INSERT INTO ptable VALUES(3,1,'c')
INSERT INTO ptable VALUES(4,1,'d')
INSERT INTO ptable VALUES(5,2,'e')
INSERT INTO ptable VALUES(6,3,'f')
INSERT INTO ptable VALUES(7,3,'g')
INSERT INTO ptable VALUES(8,4,'h')
GO

--查询出1结点的所有子结点
with tmp as(select * from ptable where id = 1
union all select ptable.* from tmp, ptable where tmp.id = ptable.pid
)
select * from tmp

--查询出8结点的所有父结点
with tmp as(select * from ptable where id = 8
union all select ptable.* from tmp, ptable where tmp.pid = ptable.id
)
select * from tmp;

--递归删除1结点和所有子结点的语句：
with tmp as(select * from ptable where id = 1
union all select ptable.* from tmp, ptable where tmp.id = ptable.pid
)
delete from ptable where exists (select id from tmp where tmp.id = ptable.id)

转载于:https://www.cnblogs.com/pato/archive/2012/03/31/2427210.html