MySQL ORDER BY 后的 GROUP BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7310653/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
GROUP BY after ORDER BY
提问by David Rodrigues
I need to do GROUP BYafter ORDER BY. I don't understand why MySQL doesn't support that.
This is my code:
我需要做的GROUP BY之后ORDER BY。我不明白为什么 MySQL 不支持。这是我的代码：
SELECT
`pages`.`id`,
`contents`.`id_language`,
[...]
[...]
ORDER BY
FIND_IN_SET(`languages`.`id`, '3') DESC
[the GROUP BY]
The results will be something like this:
结果将是这样的：
id | id_language | ...
1 3
1 1
2 3
2 5
2 1
I need to group by ID, I need only the first result and I need to save in a view. I can't use a SUBQUERY because of that.
我需要按 ID 分组，我只需要第一个结果，我需要保存在视图中。因此我不能使用 SUBQUERY。
The result need to be:
结果必须是：
id | id_language | ...
1 3
2 3
Note: Don't get confused by id_language = 3, because it isn't a rule.
注意：不要被 混淆id_language = 3，因为它不是规则。
回答by BitMaese
SELECT id, idl
FROM (SELECT
`pages`.`id` as id,
`contents`.`id_language` as idl,
[...]
[...]
ORDER BY
FIND_IN_SET(`languages`.`id`, '3') DESC
) d
GROUP BY d.id
回答by Jonathan Weatherhead
Group By will group result sets, and is generally used for aggregation. Order By is the way that results are sorted.
Group By 将对结果集进行分组，一般用于聚合。Order By 是对结果进行排序的方式。
回答by Robert Martin
You may want an additional column in your original query that you GROUP BY, along with whatever you're currently grouping by. That column, when grouped, could then be used to order afterward. For instance:
您可能需要在原始查询中添加一个 GROUP BY 列，以及您当前分组依据的任何内容。该列在分组后可用于之后进行排序。例如：
SELECT
SUM(IF(`languages`.`id` = 3, 1, 0)) AS languageOrder,
`pages`.`id`,
`contents`.`id_language`,
[...]
[...]
[GROUP BY...]
ORDER BY languageOrder DESC
I would intend for languageOrder to be positive for groups that contain language #3, 0 otherwise. So groups that contain language 3 will be at the top.
我希望 languageOrder 对于包含语言 #3, 0 的组为正。因此，包含语言 3 的组将位于顶部。
回答by ajreal
Very amusing, try
很有趣，试试
select * from your_table
where id_language=3
order by id;
As far I can tell, the rule set is id_language=3,
which make no differences from using where
据我所知，规则集是id_language=3，
这与使用没有区别where
