MySQL 首先按...排序，然后按 GROUP BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3695502/
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
FIRST ORDER BY ... THEN GROUP BY
提问by Jacco
I have two tables, one stores the users, the other stores the users' email addresses.
我有两个表，一个存储用户，另一个存储用户的电子邮件地址。
- table users: (
userId,username,etc) - table userEmail: (
emailId,userId,email)
- 表用户：(
userId,username,etc) - 表 userEmail: (
emailId,userId,email)
I would like to do a query that allows me to fetch the latest email address along with the user record.
I'm basically looking for a query that says
我想做一个查询，允许我获取最新的电子邮件地址和用户记录。
我基本上是在寻找一个查询，说
FIRST ORDER BY userEmail.emailId DESC
THEN GROUP BY userEmail.userId
This can be done with:
这可以通过以下方式完成：
SELECT
users.userId
, users.username
, (
SELECT
userEmail.email
FROM userEmail
WHERE userEmail.userId = users.userId
ORDER BY userEmail.emailId DESC
LIMIT 1
) AS email
FROM users
ORDER BY users.username;
But this does a subquery for every row and is very inefficient. (It is faster to do 2 separate queries and 'join' them together in my program logic).
但这对每一行都做一个子查询，效率很低。（在我的程序逻辑中执行 2 个单独的查询并将它们“连接”在一起会更快）。
The intuitive query to write for what I want would be:
为我想要的内容编写的直观查询是：
SELECT
users.userId
, users.username
, userEmail.email
FROM users
LEFT JOIN userEmail USING(userId)
GROUP BY users.userId
ORDER BY
userEmail.emailId
, users.username;
But, this does not function as I would like. (The GROUP BYis performed before the sorting, so the ORDER BY userEmail.emailIdhas nothing to do).
但是，这并不像我想要的那样起作用。（GROUP BY在排序之前执行，所以ORDER BY userEmail.emailId无关）。
So my question is:
Is it possible to write the first query without making use of the subqueries?
所以我的问题是：
是否可以在不使用子查询的情况下编写第一个查询？
I've searched and read the other questions on stackoverflow, but none seems to answer the question about this query pattern.
我已经搜索并阅读了有关 stackoverflow 的其他问题，但似乎没有人回答有关此查询模式的问题。
采纳答案by Cowan
But this does a subquery for every row and is very inefficient
但这对每一行都做一个子查询，效率很低
Firstly, do you have a query plan / timings that demonstrate this? The way you've done it (with the subselect) is pretty much the 'intuitive' way to do it. Many DBMS (though I'm not sure about MySQL) have optimisations for this case, and will have a way to execute the query only once.
首先，您是否有一个查询计划/时间来证明这一点？您完成它的方式（使用子选择）几乎是“直观”的方式。许多 DBMS（虽然我不确定 MySQL）对这种情况进行了优化，并且可以只执行一次查询。
Alternatively, you should be able to create a subtable with ONLY (user id, latest email id)tuples and JOINonto that:
或者，您应该能够创建一个只有(user id, latest email id)元组的子表，JOIN然后：
SELECT
users.userId
, users.username
, userEmail.email
FROM users
INNER JOIN
(SELECT userId, MAX(emailId) AS latestEmailId
FROM userEmail GROUP BY userId)
AS latestEmails
ON (users.userId = latestEmails.userId)
INNER JOIN userEmail ON
(latestEmails.latestEmailId = userEmail.emailId)
ORDER BY users.username;
回答by NamshubWriter
If this is a query you do often, I recommend optimizing your tables to handle this.
如果这是您经常执行的查询，我建议优化您的表来处理此问题。
I suggest adding an emailIdcolumn to the userstable. When a user changes their email address, or sets an older email address as the primary email address, update the user's row in the userstable to indicate the current emailId
我建议emailId在users表格中添加一列。当用户更改其电子邮件地址或将旧电子邮件地址设置为主电子邮件地址时，更新users表中用户的行以指示当前emailId
Once you modify your code to do this update, you can go back and update your older data to set emailIdfor all users.
修改代码以执行此更新后，您可以返回并更新旧数据以设置emailId所有用户。
Alternatively, you can add an emailcolumn to the userstable, so you don't have to do a join to get a user's current email address.
或者，您可以email向users表中添加一列，这样您就不必进行联接来获取用户的当前电子邮件地址。
