有如下两表: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 * FROM a LEFT JOIN b ON b.aid = a.aid ORDER BY b.time DESC
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 * FROM a LEFT JOIN b ON b.aid = a.aid ORDER BY b.time DESC
a.title,
b.image,
(
SELECT time
FROM b
WHERE aid=id
ORDER BY time DESC
LIMIT 1
)
FROM a
LEFT JOIN b
ON a.aid = b.aid
GROUP BY a.aid;