mysql结果group_相同的查询返回不同的结果（MySQL Group By）

This only happens for queries that force GROUP BY after ORDER BY.

Goal:

Get latest balance for each unit for the given cardID.

Table:

cardID | unit | balance | date

--------|-----------|-----------|--------------

A1 | DEPOSIT | 100 | 2016-05-01

A1 | DEPOSIT | 90 | 2016-05-02

A1 | DEPOSIT | 80 | 2016-05-03

A1 | DEPOSIT | 75 | 2016-05-04

A1 | MINUTE | 1000 | 2016-05-01

A1 | MINUTE | 900 | 2016-05-02

A1 | MINUTE | 800 | 2016-05-03

Query:

SELECT * FROM (

SELECT unit, balance

FROM cardBalances

WHERE cardID = 'A1'

ORDER BY date DESC

) AS cb

GROUP BY cb.unit;

Expected Result (MySQL v5.5.38):

unit | balance

---------|-----------

DEPOSIT | 75

MINUTE | 800

Unexpected Result (MySQL v5.7.13):

unit | balance

---------|-----------

DEPOSIT | 100

MINUTE | 1000

After upgrading to MySQL v5.7.13, the result returns the initial balances; as if no deduction occurred for the given card.

Is this a bug in MySQL version?

Would you suggest any other, more reliable way to solve this?

解决方案

This is a bug in your use of the database. MySQL is quite explicit that when you include columns in the SELECT clause in an aggregation query -- and they are not in the GROUP BY -- then they come from indeterminate rows.

Such syntax is specific to MySQL. It is not only a bad idea to learn, but it simply normally not work in other databases.

You can do what you want in various ways. Here is one:

SELECT cb.*

FROM cardBalances cb

WHERE cardId = 'A1' AND

cb.date = (SELECT MAX(date)

FROM cardBalances cb2

WHERE cb2.cardId = 'A1' AND cb2.unit = cb.unit

);

This has the advantage that it can use an index on cardBalances(unit, CardId, date).