我有两张表,分别是account和balance
/---------------------\
| cid | name | mobile |
|---------------------|
| 1 | ABC | 12345 |
|---------------------|
| 2 | XYZ | 98475 |
\---------------------/
/----------------------------\
| date | cid | balance |
|----------------------------|
| 2013-09-19 | 1 | 5000 |
|----------------------------|
| 2013-09-19 | 2 | 7000 |
|----------------------------|
| 2013-09-20 | 1 | 300 |
|----------------------------|
| 2013-09-20 | 2 | 4500 |
|----------------------------|
| 2013-09-21 | 2 | 600 |
\----------------------------/
我希望将这两个表连接起来,并获取特定cid的最大日期的余额。
输出结果为 -
/--------------------------------------------\
| cid | name | mobile | date | balance |
|--------------------------------------------|
| 1 | ABC | 12345 | 2013-09-20 | 300 |
|--------------------------------------------|
| 2 | XYZ | 98475 | 2013-09-21 | 600 |
\--------------------------------------------/
SELECT a.cid, a.name, a.mobile, b1.date, b1.balance FROM account a JOIN balance b1 ON a.cid = b1.cid JOIN (SELECT cid, MAX(Date) As maxDate FROM balance GROUP BY cid) b2 ON b1.cid = b2.cid AND b1.date = b2.maxDate
。我来回测试它,得到了相同的结果,只是想知道为什么你的答案更好?谢谢。 - fun2life