有一表的结构如下:
ID:主键、自增。。。SortID:排序规则
----------------------------------------------
ID Code Name SortID
1 001 News 1
2 001001 News1-1 1
3 001002 News1-2 2
4 001002001 News1-2-1 1
5 002 Soft 2
6 002001 Soft1-1 1
7 002002 Soft1-2 2
8 002003 Soft1-3 3
9 002003001 Soft1-3-1 1
10 002003001001 Soft1-3-1-1 1
11 002004 Soft1-4 4
12 002002001 Soft1-2-1 1
...
----------------------------------------------
目的:想用CTE来实现递归查询出所有父级菜单和它的子集菜单的记录
求大神
------解决方案--------------------
if OBJECT_ID('tempdb..#t') is not null
drop table #t
select 1 ID, '001' Code , 'News' Name , 1 SortID
into #t
union all select 2 , '001001' , 'News1-1' , 1
union all select 3 , '001002' , 'News1-2' , 2
union all select 4 , '001002001' , 'News1-2-1' , 1
union all select 5 , '002' , 'Soft' , 2
union all select 6 , '002001' , 'Soft1-1' , 1
union all select 7 , '002002' , 'Soft1-2' , 2
union all select 8 , '002003' , 'Soft1-3' , 3
union all select 9 , '002003001' , 'Soft1-3-1' , 1
union all select 10 , '002003001001', 'Soft1-3-1-1', 1
union all select 11 , '002004' , 'Soft1-4' , 4
union all select 12 , '002002001' , 'Soft1-2-1' , 1
;with t as
(
select *,levelid=1,parentid=0
from #t
where ID in (1,5)
union all
select b.ID,b.Code,b.Name,b.SortID,levelid=a.levelid+1,parentid=a.id
from t a
join #t b
on a.Code= substring(b.code,1,LEN(a.code)) and LEN(b.code)>LEN(a.code)
)
select Id,code,name,sortid,max(levelid) as levelid,max(parentid) as parentid
from t
group by Id,code,name,sortid
order by left(code,4),levelid
------解决方案--------------------
这个是递归查询:
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb
(
id int,
code varchar(100),
name varchar(100),
sortid int
)
insert into tb
select 1 , '001' , 'News' Name , 1 SortID
union all select 2 , '001001' , 'News1-1' , 1
union all select 3 , '001002' , 'News1-2' , 2
union all select 4 , '001002001' , 'News1-2-1' , 1
union all select 5 , '002' , 'Soft' , 2
union all select 6 , '002001' , 'Soft1-1' , 1
union all select 7 , '002002' , 'Soft1-2' , 2
union all select 8 , '002003' , 'Soft1-3' , 3
union all select 9 , '002003001' , 'Soft1-3-1' , 1
union all select 10 , '002003001001', 'Soft1-3-1-1', 1
union all select 11 , '002004' , 'Soft1-4' , 4