sql递归查询的例子，图文并茂，通俗易懂，有需要的朋友参考下了。
首先，创建数据表ptable
复制代码 代码示例:
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
如下图：
下面开始sql递归查询的例子。
1、查询出1结点的所有子结点
复制代码 代码示例:
--查询出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
如下图：
2、查询出8结点的所有父结点
复制代码 代码示例:
--查询出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;
如下图：
3、递归删除1结点和所有子结点
复制代码 代码示例:
--递归删除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)
如下图：
