第一次订购...然后分组 [英] FIRST ORDER BY ... THEN GROUP BY

问题描述

我有两个表，一个存储用户，另一个存储用户的电子邮件地址.

I have two tables, one stores the users, the other stores the users' email addresses.

• 表用户:(userId，username，etc)
• 表userEmail:(emailId，userId，email)

• table users: (userId, username, etc)
• table 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).

编写我想要的内容的直观查询将是:

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;

但是，这不起作用. (GROUP BY是在排序之前执行的，因此ORDER BY userEmail.emailId没有任何关系).

But, this does not function as I would like. (The GROUP BY is performed before the sorting, so the ORDER BY userEmail.emailId has nothing to do).

所以我的问题是:
是否可以在不使用子查询的情况下编写第一个查询?

So my question is:
Is it possible to write the first query without making use of the subqueries?

我已经搜索并阅读了关于stackoverflow的其他问题，但似乎没有人回答有关此查询模式的问题.

I've searched and read the other questions on stackoverflow, but none seems to answer the question about this query pattern.

推荐答案

但这会为每行执行一次子查询，并且效率很低

But this does a subquery for every row and is very inefficient

首先，您有一个证明这一点的查询计划/时间安排吗?您完成操作的方式(使用子选择)几乎是执行操作的直观"方式.许多DBMS(尽管我不确定MySQL)都针对这种情况进行了优化，并且将只能执行一次查询.

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.

或者，您应该能够仅在其中创建(user id, latest email id)元组和JOIN的子表:

Alternatively, you should be able to create a subtable with ONLY (user id, latest email id) tuples and JOIN onto that:

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;

这篇关于第一次订购...然后分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！