MySQL 排序分组依据结果

Question

我有以下表格结构:

table_1

id     title
------------
155    item1
156    item2
157    item3

table_2

id     model
------------
155    100
156    100
157    200

table_3

id     cid
------------
155    5
156    5
157    5

我想按模型(来自表_2)对我的结果进行分组并确保它返回最高的 id(按 id 降序排列) .

我尝试了以下方法，但order by 子句似乎不起作用:

SELECT a.id, b.model FROM table_1 as a
INNER JOIN table_2 as b on b.id = a.id
INNER JOIN table_3 as c on c.id = a.id
WHERE c.cid = 5
GROUP BY b.model
ORDER BY a.id desc

我做错了什么？

Answer 1

SELECT max(a.id), b.model FROM table_1 as a
INNER JOIN table_2 as b on b.id = a.id
INNER JOIN table_3 as c on c.id = a.id
WHERE c.cid = 5
GROUP BY b.model
ORDER BY max(a.id) desc

此处演示:http://sqlfiddle.com/#!9/afb0b/2

这有效而您的初始尝试没有的原因是 GROUP BY 子句中不存在的任何字段，或未在聚合函数中使用的字段(例如 MAX)，将具有不确定的值(即，mysql 引擎不保证您将从组中获得哪个值)。

MySQL GROUP BY Handling

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

事实上，MySQL 是为数不多的(唯一的？)sql 数据库之一，它可以让您在 select 子句中放置一个非聚合、非分组的字段而不会出错。