sqlserver2005 递归查询

最新推荐文章于 2022-04-26 17:45:00 发布

原创最新推荐文章于 2022-04-26 17:45:00 发布 · 755 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #join #null

本文介绍使用递归查询从上往下及从下往上的搜索方法，通过具体实例展示如何利用SQL的WITH子句实现多层次的数据检索。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--递归查询:从上往下搜索
with playadminCTE(plaparentid,plaid,platask,ilayer)
as
(
select plaparentid,plaid,platask,ilayer=1 from playadmin where plapadid=325
and plaparentid is null and plaid=307
union all
select a.plaparentid,a.plaid,a.platask,ilayer+1
from playadmin a INNER join playadminCTE b on
a.plaparentid=b.plaid --and b.plaid=6295
)

--递归查询:从下往上搜索
with playadminCTE(plaparentid,plaid,platask,ilayer)
as
(
select plaparentid,plaid,platask,ilayer=1 from playadmin where plaparentid is not null and plaid=6296
union all
select a.plaparentid,a.plaid,a.platask,ilayer=ilayer+1
from playadmin a INNER join playadminCTE b on
b.plaparentid=a.plaid
)