mysql 连接取最大值,MySQL连接两个表，另一个字段具有最大值

I have two table account and 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 |

\----------------------------/

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

Output result as -

/--------------------------------------------\

| 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;

Output:

CID

NAME

MOBILE

DATE

BALANCE

1

ABC

12345

September, 20 2013 00:00:00+0000

300

2

XYZ

98475

September, 21 2013 00:00:00+0000

600