就一张表,只有两列(id,pid)
id pid
101 null
10101 101
1010101 10101
10102 101
102 null
10201 102
(id为编号,pid为父类)要求算出查询的类的最顶级
例如:查询: 101 10101 10201
结果应为:101 10201
又如:101 10101 1010101 102 10201
结果应为:102 102
id pid
101 null
10101 101
1010101 10101
10102 101
102 null
10201 102
(id为编号,pid为父类)要求算出查询的类的最顶级
例如:查询: 101 10101 10201
结果应为:101 10201
又如:101 10101 1010101 102 10201
结果应为:102 102
id pid deep
101 null 0
10101 101 1
1010101 10101 2
10102 101 1
102 null 0
10201 102 1
得到deep和pid
d=deep;
nextpid=pid;
while(d!=0)
{
select deep,pid from d where id =nextpid;
d=deep;
nextpid=pid;
}
last
nextpid=101
----创建测试数据
if object_id('tbTest') is not null
drop table tbTest
if object_id('spGetChildren') is not null
drop proc spGetChildren
GO
create table tbTest(id int, upperid int)
insert tbTest
select 1, 2 union all
select 3, 2 union all
select 4, 1 union all
select 5, 3
GO
----创建存储过程
create proc spGetChildren @id int
as
declare @t table(id int)
insert @t select id from tbTest where upperid = @id
while @@rowcount > 0
insert @t select a.id from tbTest as a inner join @t as b
on a.upperid = b.id and a.id not in(select id from @t)
select * from @t
GO
----执行存储过程
declare @upperid int
set @upperid = 2
EXEC spGetChildren @upperid
----清除测试环境
drop proc spGetChildren
drop table tbTest
/*结果
id
-----------
1
3
4
5
*/
http://user.qzone.qq.com/403166800/infocenter?ptlang=2052
这个问题用with递归比较适合,自己去试试吧