在MySQL中如何使用另一个字段的最大值连接两个表？

Question

我有两张表，分别是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   |
\--------------------------------------------/

- newcomer

Answer 1

你需要像这样使用两个子查询：

SELECT a.cid, a.name, a.mobile, b.date, b.balance
FROM account a 
JOIN
(
    SELECT b1.* FROM balance b1
    JOIN
    (
      SELECT cid, MAX(Date) As maxDate
      FROM balance
      GROUP BY cid
    ) b2
    ON b1.cid = b2.cid
    AND b1.date = b2.maxDate
) b
ON a.cid = b.cid;

输出:

CID	名称	手机	日期	余额
1	ABC	12345	2013年9月20日 00:00:00+0000	300
2	XYZ	98475	2013年9月21日 00:00:00+0000	600

请查看此SQLFiddle

编辑

如评论中所讨论的，此查询也可以只使用一个子查询来编写：

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

请查看已调整的SQLFiddle

- Himanshu

---

感谢 @hims056 我得到了我想要的结果，但无法保存为视图。当我尝试保存为视图时，我收到了一个错误消息：-1349-视图的 SELECT 子句包含一个子查询。请帮忙解决。我需要将此查询保存在 VIEW 中。 - newcomer

@新手 - 视图不能包含子查询。但是，您可以为每个上面的子查询创建视图，然后在最终视图中使用这些视图。在此处查看一些提示。 - Himanshu

谢谢，非常感谢！ - piotr_cz

非常感谢您的帮助。我在类似的情况下卡了一个多小时。 - Xishan

@HimanshuJansari：抱歉，我知道你早就回答了这个问题，但我遇到了类似的问题，你的答案帮助我构建了我的查询。由于复制粘贴错误，我想出了这个解决方案: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

@fun2life：实际上，你的查询比我的更好。我不确定为什么当时我使用了两个嵌套子查询而不是只用一个。但是，少使用子查询肯定是更好的。所以你的解决方案比我好得多。你可以发布你的答案并说明相同的事情，或者编辑我的答案，因为OP没有活动，所以你的解决方案可以在被接受的答案中找到。 - Himanshu

Answer 2

SELECT a.cid, a.name, a.mobile, MAX(b.date), b.balance 
FROM account AS a
INNER JOIN balance AS b
WHERE a.cid=b.cid 
GROUP BY cid;

抱歉，我没有注意到第三个表格中的余额列。

SELECT a.cid, a.name, a.mobile, b.date, b.balance 
FROM account AS a
INNER JOIN (
      SELECT c.date, c.cid, c.balance FROM balance AS c
      INNER JOIN (
            SELECT cid AS cid2, MAX(date) AS date2
            FROM balance
            GROUP BY cid2) AS d
ON c.cid=d.cid2 
AND c.date=d.date2
) AS b
ON a.cid=b.cid 
GROUP BY cid;--

- Onel Sarmiento

---

你好，Leo。在不对每个字段进行分组的情况下使用聚合函数会为这些字段提供随机值。 - Himanshu

Answer 3

以下语句应该能够给您所需的结果：

SELECT cid, name, mobile, MAX(date), blance  
FROM account
LEFT JOIN balance
ON account.cid = balance.cid
GROUP BY balance.cid

- user3108497