select A.id,A.name,A.r1.B.state,B.date from A left join B on A.r1=B.r1
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
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
create table A( id int, name varchar(10), r1 int ) goinsert 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 gocreate table B( r1 int, state varchar(10), date varchar(10) ) goinsert 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 godrop 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
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
from A left join B on A.r1=B.r1
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
,b1.state,b1.date
from a
outer apply (
select top 1 *
from b
where r1= a.r1
order by date desc
) as b
create table A(
id int,
name varchar(10),
r1 int
)
goinsert 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
gocreate table B(
r1 int,
state varchar(10),
date varchar(10)
)
goinsert 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
godrop 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
from a , (SELECT MAX(date) date,state,r1 from b group by r1 ) c
where a.r1*=c.r1