mysql - 为什么需要 'ORDER BY' 才能从 MySQL 连接获得正确的结果？

Question

我有以下查询:

SELECT t.ID, t.caseID, time
FROM tbl_test t
INNER JOIN (
    SELECT ID, MAX( TIME ) 
    FROM tbl_test
    WHERE TIME <=1353143351
    GROUP BY caseID 
    ORDER BY caseID DESC -- ERROR HERE!
) s
USING (ID)

似乎只有在内部连接中使用 ORDER BY 才能得到正确的结果。这是为什么？我正在使用加入的 ID，因此该订单应该不会产生任何影响。如果我删除订单，我会从数据库中得到太旧的条目。ID是主键，caseID是一种具有不同时间戳的多个条目的对象。

Answer 1

这个查询是不明确的:

SELECT ID, MAX( TIME ) 
FROM tbl_test
WHERE TIME <=1353143351
GROUP BY caseID 

这是不明确的，因为它不保证返回出现 MAX(TIME) 的行的 ID。它为 caseID 的每个不同值返回 MAX(TIME)，但其他列(如 ID)的值是从组成员中任意选择的。

在实践中，MySQL 选择它在组中首先找到的行，因为它按存储顺序扫描行。

例子:

caseID  ID  time
  1     10  15:00
  1     12  18:00
  1     14  13:00

最大时间是 18:00，这是 ID 为 12 的行。但是查询将返回 ID 10，因为它是组中的第一个。如果您要使用 ORDER BY 反转顺序，它将返回 ID 14。仍然不是找到最大时间的行，但它来自行组的另一端。

您的查询适用于 ORDER BY caseID DESC，因为巧合的是，您的时间值随着 ID 的增加而增加。

这种查询实际上是标准 SQL 和大多数其他品牌的 SQL 数据库中的错误。 MySQL 允许这样做，相信您知道如何形成明确的查询。

修复方法是仅在选择列表中使用明确的列，也就是说，如果它们在 GROUP BY 子句中，则保证每个组只有一个不同的值:

SELECT caseID, MAX( TIME ) 
FROM tbl_test
WHERE TIME <=1353143351
GROUP BY caseID