sql查询父id下的最后一个子id

SQL code

composite   single
A            B
B            C
C            D
AA           BB

参数为 A 查出 D
参数为 B 查出 D
参数为 AA 查出 BB。

要用标准sql来写，存储过程也行。不知道有什么好的办法。

SQL code

;with f as
(
select * from tb where composite=@composite
union all
select a.* from tb a join f b on a.single=b.composite
)
select * from f

[code=SQL]
create table #t(id int,childid int)
insert into #t
select 1 , 2 union all
select 2 , 3 union all
select 3 , 4 union all
select 5 , 6

;with t as
(
select flag = 1 , * from #t where id = 1 --换成实际参数
union all
select flag = t.flag + 1 ,#t.* from #t inner join t on #t.id = t.childid
)
select top 1 childid from t order by flag desc

drop table #t
----------------------------------------
childid
-----------
4

(1 行受影响)

/code]

SQL code

create table #t(id int,childid int)
insert into #t
select 1 , 2 union all
select 2 , 3 union all
select 3 , 4 union all
select 5 , 6

;with t as
(
select flag = 1 , * from #t where id = 1 --换成实际参数
union all
select flag = t.flag + 1 ,#t.* from #t inner join t on #t.id = t.childid
)
select top 1 childid from t order by flag desc

drop table #t
----------------------------------------
childid
-----------
4

(1 行受影响)

楼上的两位大哥，;with f as是什么意思，我用的是mysql，支持你们的写法吗

参照

参考下贴中的方法。

http://blog.csdn.net/acmain_chm/article/details/4142971
MySQL中进行树状所有子节点的查询

我只要最后一个子节点，不要所有的。。