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

问题描述

只有在 ORDER BY 之后强制 GROUP BY 的查询才会发生。

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

获取最新 余额 code> unit 给定 cardID 。

Get latest balance for each unit for the given cardID.

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;

预期结果（MySQL v5.5.38）：

Expected Result (MySQL v5.5.38):

unit     |  balance
---------|-----------
DEPOSIT  |  75
MINUTE   |  800

意外结果（MySQL v5.7.13）：

Unexpected Result (MySQL v5.7.13):

unit     |  balance
---------|-----------
DEPOSIT  |  100
MINUTE   |  1000

升级到MySQL v5.7.13后，结果返回初始余额;就好像没有扣除指定的卡片一样。

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

这是MySQL版本的错误吗？

您会建议使用其他更可靠的方法解决这个问题？

Is this a bug in MySQL version?
Would you suggest any other, more reliable way to solve this?

推荐答案

这是您使用数据库时的一个错误。 MySQL非常明确，当你在聚合查​​询中的 SELECT 子句中包含列时，它们不在 GROUP BY中 - 然后它们来自 indeterminate 行。

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.

这种语法特定于MySQL。这不仅仅是一个好主意，但它通常不适用于其他数据库。

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

这样做的好处是可以在 cardBalances上使用索引（单位， CardId，日期）。

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

这篇关于相同的查询返回不同的结果（MySQL Group By）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！