WITH lmenu(name,senior,level) as
(
SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL
UNION ALL
SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
where a.senior = b.name
)
SELECT * from lmenu

--你这个是递归吗？


给你一个递归的例子，最基础的递归：

--> 测试数据：[tbl]
go
if object_id('[tbl]') is not null 
drop table [tbl]
go
create table [tbl](
[name] varchar(1),
[date] varchar(5),
[num] int
)
go
insert [tbl]
select 'a','1-1号',1 union all
select 'b','1-2号',4 union all
select 'a','1-3号',8 union all
select 'a','1-4号',5 union all
select 'b','1-5号',6 union all
select 'b','1-6号',9

;with t
as(
select ROW_NUMBER()over(partition by name
order by [date]) as id,
*,num  as total from tbl
),
m as(
select id,name,[date],num,total from t where id=1
union all
select a.id,a.name,a.[date],a.num,b.total+a.num from t a
inner join m b on a.id=b.id+1 and a.name=b.name
)
select name,[date],num,total from m order by name

/*
name    date    num    total
a    1-3号    8    8
a    1-4号    5    13
a    1-1号    1    14
b    1-2号    4    4
b    1-5号    6    10
b    1-6号    9    19
*/