有点难道的sql语句，请大家来试下！
declare @test table(id int, title varchar(20))
declare @sub table(id int,test_id int, subtitle varchar(20))
insert into @test values(1, 'firstTitle ')
insert into @sub values(1,1, 'firstSubTitle ')
insert into @sub values(2,1, 'secondSubTitle ')
insert into @test values(2, 'secondTitle ')
insert into @sub values(3,2, 'secondfirstSubTitle ')
insert into @sub values(4,2, 'secondsecondSubTitle ')
select a.*,b.id as sub_id,b.subtitle
from @test a,@sub b
where a.id=b.test_id
这样得到的结果是：
1firstTitle1firstSubTitle
1firstTitle2secondSubTitle
2secondTitle3secondfirstSubTitle
2secondTitle4secondsecondSubTitle
但是我想要的结果却是：
1firstTitle2secondSubTitle
2secondTitle4secondsecondSubTitle
请问sql语句该怎么写？
[解决办法]
select a.*,b.id as sub_id,b.subtitle
from @test a,@sub b
where a.id=b.test_id
and not exists(select 1 from @sub where b.test_id = test_id and len(b.subtitle) < len(subtitle))
[解决办法]
select m.* from
(
select a.*,b.id as sub_id,b.subtitle
from @test a,@sub b
where a.id=b.test_id
) m,
(
select n.id , max(sub_id) sub_id from
(
select a.*,b.id as sub_id,b.subtitle
from @test a,@sub b
where a.id=b.test_id
) n group by id
) p
where m.id = p.id and m.sub_id = p.sub_id
[解决办法]
如果是取最后的，加上
and not exists(select 0 from @sub c where c.test_id=b.test_id and c.id <b.id)
[解决办法]
select a.*,d.id as sub_id,d.subtitle from test a,
(
select b.* from sub b,
(
select test_id , max(id) id from sub group by test_id
) c
where b.test_id = c.test.id and b.id = c.id
) d
where a.id = d.test_id
