树型结构的sql查询

1、从子节点向根节点查询（可能还有更好的方法）

递归（简单性能高）:

注意 1递归只能在as()后面第一条语句使用再后面使用会失效 2一个with可以定义多个as :with cte1 as() ,cte2 as()

参考：http://www.cnblogs.com/guoysh1987/archive/2011/12/23/2299379.html

--递归查询   cte_parent 相当于是一个临时表 存储上一次查询的结果集（每次递归就覆盖） 
 --其中最少包括两个成员 第一个是起点  第二个是递归（循环成员）
 --没有显式的结束条件 会查询到结果是空为止，所以可以在递归部分中写where 条件使结果为空 循环终止  
 --也可以通过MAXRECURION() 函数设置最大递归次数
with cte_parent(id,title,parent)
as
(
    --起始条件
    select id,title,parent
    from tb_menu
    where id = 44   --列出子节点查询条件
    union all
    --递归条件 ( 有按层级递归  比如公司-部门 -岗位-人 就递归三次【查部门、查岗位、查人，包括起始条件的一次】)    
    select a.id,a.title,a.parent
    from tb_menu a
    inner join 
 cte_parent b         
    on a.id=b.parent -- where a.parent>=2  --在这里可以加结束条件  --执行递归，更新cte的结果集【覆盖而不是追加】 每执行一次 比上次多一个层级 执行到和上次结果一样没有更新的记录了就停止）
 select * from cte_parent OPTION (MAXRECURSION 2);-- 最后查询出结果集（在这里可以加where 过滤结果）在这里可以限制递归次数 （不算起始条件那一次）

传统方法（临时表）：

create table #TempTB ([Parent_ID] int null,   
        [ID] int null)         <span style="font-family: Arial, Helvetica, sans-serif;"> --创建临时表  存储查询结果</span>
insert into #TempTB([Parent_ID],[ID])     
select pid,id
from  [dbo].[area]
where id = '621222'     --传入开始节点id
declare @i int
set @i = 0
declare @n varchar(20)
set @n = '621200'      --开始节点pid    临时变量 用来保存上一步的pid
while @i = 0<span style="white-space:pre"></span>   --循环查询  直到没结果
begin
    if exists (select *
        from  [dbo].[area]  where id=@n)
    begin
        insert into #TempTB([Parent_ID],[ID])<span style="white-space:pre"></span>--插入临时表
        select PID ,ID
        from [dbo].[area] where ID=@n
set @n=( select PID <span style="white-space:pre"></span>--<span style="white-space:pre"></span>设置变量@n保存此步pid
        from [dbo].[area] where ID=@n)
print @n

    end 
else 
    begin
        set @i = 1<span style="white-space:pre"></span>--没结果  结束标记为1
    end
end
select DISTINCT ID,Parent_ID from #TempTB<span style="white-space:pre"></span>--查询临时表得出最后结果

drop table #TempTB  <span style="white-space:pre"></span>--删除临时表

2、从根节点向子节点查询

with cte_child(id,title,parent,level)
as
(
    --起始条件
    select id,title,parent,0 as level
    from tb_menu
    where id = 6--列出父节点查询条件
    union all
    --递归条件
    select a.id,a.title,a.parent,b.level+1
    from tb_menu a
    inner join 
    cte_child b
    on ( a.parent=b.id)
)
select  * from cte_child;

3、根据其中一个节点分别向上和向下查询

秒客网

树型结构的sql查询

相关文章