相同的查询返回不同的结果（MySQL Group By） [英] Same Query returns different results (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）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！