◆◆◆ 求一个MySQL查询语句

Question

有如下两表:a表:
aid    title
-------------
 1      111
 2      222b表:
bid     aid    image           time
-----------------------------------------
 1       2     1.gif   2007-08-08 00:00:00
 2       2     2.gif   2007-08-09 00:00:00
 3       2     3.gif   2007-08-08 00:00:00
如何得到如下的结果呢? 
(即a表与b表关联,取出b表中最新的一条数据,且a表中的数据不可重复出现)aid    title   image            time
-----------------------------------------
 1     111      NULL            NULL
 2     222      2.gif       2007-08-09 00:00:00
用下面的语句是错误的 ;-(
SELECT a.aid, a.title, b.image, b.time FROM a LEFT JOIN b ON b.aid = a.aid ORDER BY b.time DESC

Answer 1

select a.aid,a.title,image,time
from a left join b
on a.aid=b.aid
and  time in(select top 1 time from b order by time desc)
aid         title       image                                    time                                                   
----------- ----------- ---------------------------------------- ------------------------------------------------------ 
1           111         NULL                                     NULL
2           222         2.gif                                    2007-08-09 00:00:00.000（所影响的行数为 2 行）

Answer 2

楼上语句可能有点问题因为b表可能为多行:
bid     aid    image           time
-----------------------------------------
 1       2     1.gif   2007-08-08 00:00:00
 2       2     2.gif   2007-08-09 00:00:00
 3       2     3.gif   2007-08-08 00:00:00
 4       3     4.gif   2007.........
 5       3     5.gif   2007.........
 6       4     6.gif   2007.........

Answer 3

我用数据测试过了啊.
aid         title       
----------- ----------- 
1           111
2           222
3           333（所影响的行数为 3 行）bid         aid         image                                    time                                                   
----------- ----------- ---------------------------------------- ------------------------------------------------------ 
1           2           1.gif                                    2007-08-08 00:00:00.000
2           2           2.gif                                    2007-08-09 00:00:00.000
3           2           3.gif                                    2007-08-08 00:00:00.000
1           2           1.gif                                    2007-08-08 00:10:00.000
2           2           2.gif                                    2007-08-09 00:10:00.000
3           2           3.gif                                    2007-08-08 00:10:00.000
3           2           3.gif                                    2007-08-08 00:10:00.000
3           2           3.gif                                    2007-08-08 00:10:10.000
1           3           1.gif                                    2007-08-08 00:10:00.000
2           3           2.gif                                    2007-08-09 00:10:00.000
3           3           3.gif                                    2007-08-08 00:10:00.000
3           32          3.gif                                    2007-08-08 00:10:00.000
3           3           3.gif                                    2007-08-08 00:10:10.000（所影响的行数为 13 行）
select a.aid,a.title,image,time
from a left join b
on a.aid=b.aid
and  time in(select top 1 time from b  order by time desc)
aid         title       image                                    time                                                   
----------- ----------- ---------------------------------------- ------------------------------------------------------ 
1           111         NULL                                     NULL
2           222         2.gif                                    2007-08-09 00:10:00.000
3           333         2.gif                                    2007-08-09 00:10:00.000（所影响的行数为 3 行）

Answer 4

SELECT a.aid, a.title, b.image, b.time FROM a LEFT JOIN b ON b.aid = a.aid and b.time in (select max(b.time) from b )
这个可以