create table table1
(
id varchar(10),
name varchar(10)
)
insert into table1
select '1a','a'
union all
select '2a','b'
create table [table2]
(
id varchar(10),
[2id] varchar(10),
b1 varchar(10),
b2 varchar(10),
date smalldatetime
)
insert into table2
select '1','1a','bb','bbb','2008.08.15'
union all
select '2','1a','bc','bc','2008.08.16'
create table [table3]
(
id varchar(10),
[3id] varchar(10),
c1 varchar(10),
c2 varchar(10),
date smalldatetime
)
insert into table3
select '1','2a','cc','ccc','2008.08.20'
union all
select '2','1a','cb','cd','2008.08.21'
create table table4
(
id varchar(10),
[4id] varchar(10),
d1 varchar(10),
d2 varchar(10),
date smalldatetime
)
insert into table4
select '1','1a','dd','ddd','2008.08.21'
union all
select '2','1a','d1','d2','2008.08.22'
select t1.id,t1.name,t2.b1,t2.b2,t3.c1,t3.c2,t4.d1,t4.d2 from table1 t1
left join ( select * from table2
where date in (select max(date) from table2
group by [2id])
) t2
on t1.id=t2.[2id]
left join ( select * from table3
where date in (select max(date) from table3
group by [3id])
) t3
on t1.id=t3.[3id]
left join ( select * from table4
where date in (select max(date) from table4
group by [4id])
) t4
on t1.id=t4.[4id]
id name b1 b2 c1 c2 d1 d2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1a a bc bc cb cd d1 d2
2a b NULL NULL cc ccc NULL NULL
（所影响的行数为 2 行）
个人觉得首先把table2,table3,table4查询的时候先限制好再左连接。方法不是很好，应该满足你的要求了！：）
