问个弱的查询问题

[outh]

有两张表a，b
都有id这一项
关系是1对多，即a中id唯一，b中有多个id值相同的行。
现在想选取a.id=b.id且对同一个id值只选择b中的一行。
问该如何写，谢～～

例：
a:id
1
2
3

b:index id
1 1
2 1
3 1
4 2
5 3

结果：id index
1 3
2 4
3 5

[OracleRoob]

select a.id, b.index
from a inner join (select id,max(index) as index from b group by id) t
on a.id=b.id

[OracleRoob]

--上边改点

select a.id, b.index
from a inner join (select id,max(index) as index from b group by id) t
on a.id=t.id

[OracleRoob]

create table #a([id] int)
create table #b([id] int, [index] int)
go
insert into #a([id]) values(1)
insert into #a([id]) values(2)
insert into #a([id]) values(3)

insert into #b([id],[index]) values(1,1)
insert into #b([id],[index]) values(1,2)
insert into #b([id],[index]) values(1,3)
insert into #b([id],[index]) values(2,4)
insert into #b([id],[index]) values(3,5)
go

select #a.id, t.[index]
from #a inner join (select id,max([index]) as [index] from #b group by id) t
on #a.id=t.id


drop table #a,#b

/*
结果

id index
1 3
2 4
3 5

*/

[liujx_1999]

select ID,max(index) from 表b group by ID