两表Join,只返回。。。

[zxtyhy]

有Table1

Aid AName
01 张三
02 李四
...

Table2

Aid ADate ACount
02 04-2-3 10
02 04-3-6 200
01 04-1-1 30
01 04-5-1 20
02 04-4-2 80
....
Tabel1,Table2用Aid连接
如何查询得到：
Aid AName ADate ACount
01 张三 04-5-1 20
02 李四 04-4-2 80
...
也就是Table1与Table2中的每个人员的ADate最大值Join

[Rewiah]

select a.*,b.ADate,b.ACount from table1 a,table2 b
where a.aid=b.aid
and b.ADate=(select max(ADate) from table2 where aid=b.aid)

[SE1]

select t2.aid,t1.name,t2.adate,t2.acount
from table1 t1
inner join (select * from table2 where aid=(select aid from table2 group by aid having adate=max(adate))) t2 on t1.aid=t2.aid

[zjcxc]

select a.*,b.ADate,b.ACount
from table1 a
join talbe2 b on a.Aid=b.Aid
join (
select aid,adate=max(adate) from table2 group by aid
) b1 on a.aid=b1.aid and b.adate=b1.adate

[shuiniu]

select a.*,b.Adate,Acount
from table1 a join table2 b on a.Aid = b.Aid and
b.Adate = (select max(Adate) from table2 where b.Aid = Aid)

[meilian01]

select a.aid, a.aname b.adate,b acount from table1 left join ( select * from table2 order by Adate) b on a.aid = b.aid

[funsuzhou]

select t.Aid,a.Aname,t.Adate,b.Acount from (select Aid,Adate=max(Adate) from Table2 group by Aid order by Adate desc)t left join Table1 a on t.Aid=a.Aid left join Table2 b on t.Aid=b.Aid and t.Adate=b.Adate

[funsuzhou]

上面的order by 好象有点问题

[funsuzhou]

老大那句是不是漏了 top 1?

select a.*,b.ADate,b.ACount
from table1 a
join talbe2 b on a.Aid=b.Aid
join (
select top 1 aid,adate=max(adate) from table2 group by aid
) b1 on a.aid=b1.aid and b.adate=b1.adate

[meilian01]

select a.aid, a.aname b.adate,b acount from table1 left join ( select * from table2 order by Adate Desc) b on a.aid = b.aid

[funsuzhou]

老大的没漏，不好意思。