数据库表如下 类别id 类别名称 上级类别id 1 顶级类别 0
2 类别1 1
3 类别2 1
4 类别1_1 2现在,我想查询类别下子分类的记录集 , 比如查id=1. 可以返回2、3、4想用一句sql就查出来,没一点头绪。。
2 类别1 1
3 类别2 1
4 类别1_1 2现在,我想查询类别下子分类的记录集 , 比如查id=1. 可以返回2、3、4想用一句sql就查出来,没一点头绪。。
调试欢乐多
with cte as
(
select * from tab where 上级类别id = 0
union all
select * from tab join cte on cte.类别id = tab.上级类别id
)
select * from cte
(
SELECT 类别id,类别名称,上级类别id,level=0
FROM 数据库表
WHERE 上级类别id = 1
UNION ALL
SELECT e.类别id,e.类别名称,e.上级类别id,level = d.Level + 1
FROM 数据库表 e
INNER JOIN cte d
ON e.上级类别id = d.类别id
)
SELECT 类别id,类别名称,上级类别id
FROM cte ;
GO
create table #tb(类别id int,类别名称 varchar(40),上级类别id int)
insert #tb select 1 ,'顶级类别', 0
insert #tb select 2 ,'类别1', 1
insert #tb select 3 ,'类别2', 1
insert #tb select 4 ,'类别1_1', 2;with cte as
(
select 类别id,类别名称,上级类别id,CAST(row_number() over(order by 类别id) as varbinary(max)) as sort
from #tb
where 上级类别id = 1
union all
select a.类别id,a.类别名称,a.上级类别id,sort+CAST(row_number() over(partition by a.上级类别id order by a.类别id) as BINARY)
from #tb a
inner join cte b
ON a.上级类别id = b.类别id
)select 类别id,类别名称,上级类别id
from cte
order by sort类别id 类别名称 上级类别id
----------- ---------------------------------------- -----------
2 类别1 1
4 类别1_1 2
3 类别2 1(3 行受影响)
http://blog.csdn.net/xys_777/archive/2010/06/15/5672481.aspx