两张表之间的关联 其中一张表分组取最新的一条

表A：
id        name        r1
1         aa          22
2         bb          33
3         cc          22
4         dd          44
5         ee          55

表B：
r1        state       date
22        一级        2011-2-3
22        二级        2011-5-3
33        一级        2011-3-3
44        一级        2011-3-27


以上是数据，我想得到的结果如下：
id        name        r1       state       date  
1         aa          22       二级        2011-5-3
2         bb          33       一级        2011-3-3
3         cc          22       二级        2011-5-3
4         dd          44       一级        2011-3-27
5         ee          55

哪位知道怎么解决的，帮助我一下，3Q

5 个解决方案

#1

select A.id,A.name,A.r1.B.state,B.date
from A left join B on  A.r1=B.r1

#2

select *
from 表A a left join 
 (select * from 表B t where 
        date=(select max(date) from 表B where r1=t.r1)) b
on a.r1=b.r1

#3

select a.*
,b1.state,b1.date  
from a
outer apply (
  select top 1 *
  from b
  where r1= a.r1
  order by date desc
  ) as b

#4

create table A(
	id int,
    name varchar(10),
    r1 int
)
go

insert into A(id,name,r1)
select 1,'aa',22
union all select 2,'bb',33
union all select 3,'cc',22
union all select 4,'dd',44
union all select 5,'ee',55
go

create table B(
	r1 int,
    state varchar(10),
    date varchar(10)
)
go

insert into B(r1,state,date)
select 22,'一级','2011-2-3'
union all select 22,'二级','2011-5-3'
union all select 33,'一级','2011-3-3'
union all select 44,'一级','2011-3-27'
go

--查询语句
select id,name,r1,state,date from (
select rid=row_number() over (partition by A.name order by B.date desc),
A.*,B.state,B.date from A left join B on (A.r1=B.r1)
)t 
where rid=1
go

drop table A,B
go

/*
1	aa	22	二级	2011-5-3
2	bb	33	一级	2011-3-3
3	cc	22	二级	2011-5-3
4	dd	44	一级	2011-3-27
5	ee	55	NULL	NULL

#5

select a.id,a.name,a.r1,c.state,c.date
from a , (SELECT MAX(date) date,state,r1 from b group by r1 ) c
where a.r1*=c.r1