MySQL 将两个表与另一个字段的最大值连接起来 [英] MySQL join two table with the maximum value on another field

问题描述

我有两个表账户和余额

/---------------------\
| 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 的最大日期的余额.

I would like to join this two table and get the balance of the maximum date for a particular cid.

输出结果为 -

/--------------------------------------------\
| cid | name | mobile | date       | balance |
|--------------------------------------------|
|  1  | ABC  | 12345  | 2013-09-20 |   300   |
|--------------------------------------------|
|  2  | XYZ  | 98475  | 2013-09-21 |   600   |
\--------------------------------------------/

推荐答案

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

You need to use two sub-queries like this:

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;

输出:

<头>

客户 ID	姓名	移动	日期	平衡
1	ABC	12345	2013 年 9 月 00:00:00+0000	300
2	XYZ	98475	2013 年 9 月 21 日 00:00:00+0000	600

见这个SQLFiddle

正如评论中所讨论的，这个查询也可以只用一个子查询来编写:

As discussed in the comments, this query can also be written with only one subquery:

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

这篇关于MySQL 将两个表与另一个字段的最大值连接起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！