MySQL - JOIN，GROUP BY，ORDER BY [英] MySQL - JOIN, GROUP BY, ORDER BY

问题描述

我知道，这是一个普遍的主题。

这个问题解决了一个特定的案例，但请裸露。

所以我第一次遇到的问题是， group by 子句在 order by ：

saved.recipe_id 列是一个由> UNIX_TIMESTAMP（） 生成的整数
$ b

  SELECT 
 saved.recipe_id，
 saved.`date`，
 user.user_id 
 FROM saved 
 JOIN用户
 ON user.id = saved.user_id 
 GROUP BY saved.recipe_id 
 ORDER BY saved.`date` DESC 
  

所以我尝试各种不同的可能的解决方案与子查询和其他bs。最后，我结束了在连接子句中尝试了一些不同的子查询，这要求我将的表顺序从子句添加到连接子句中。我决定尝试以下方法：

  SELECT 
 saved.recipe_id，
 saved.`date `，
 user.user_id 
 FROM用户
 JOIN已保存
 ON user.id = saved.user_id 
 GROUP BY saved.recipe_id 
 ORDER BY已保存。 `date` DESC 
  

由于某些原因，这似乎是正确排序的，但为什么？

这个改变如何使我的查询更正确地排序然后呢？

是否真的如此？或者它是否恰好是为了解决这个问题而做的？

解决方案

所以我第一次遇到的问题是，组
by子句是在订单之前执行的：

这不是问题。这就是SQL的定义和操作方式。 由组创建一个新的行集合，> 排序这些行。

这里没有订购问题。有一个了解SQL的问题。您的按顺序只是排序查询的结果。这些结果是由组由生成的，并且联接的顺序与结果无关。

您正在使用名为Hidden Columns的MySQL扩展。这是当你有一个聚合查询在中选择（或者具有或 （ sum（）等）或部分组的一部分，通过 。以下是文档中的一段引语：



MySQL扩展了GROUP BY的用法，以便选择列表可以引用未在GROUP BY子句中命名的
nonaggregated列。这意味着
前面的查询在MySQL中是合法的。您可以使用此功能
来避免不必要的列排序和
分组，以获得更好的性能。但是，这对于每个
非集合列中未在GROUP BY中命名的所有值对于每个
组都是相同的都是有用的。服务器可以自由选择每组中的任何值，因此
除非相同，否则所选的值是不确定的。
此外，通过添加ORDER BY子句，每个组中的值的选择不能是
。对结果集
进行排序发生在选择值之后，并且ORDER BY不会影响服务器选择的每个组内的
值。

假定您希望最近的日期和用户与此相关联。以下查询可以正确而一致地执行：

  SELECT将saved.recipe_id，max（saved.`date`）设为MostRecentDate ，
 substring_index（group_concat（user.user_id），'，'，1）为MostRecentUser 
 FROM用户JOIN 
保存
 ON user.id = saved.user_id 
 GROUP BY saved.recipe_id 
 ORDER BY max（saved.`date`）DESC; 
  




I know, it's a common subject.
This question addresses a specif case though, bare with me please.

---

So the problem I first had with the following query was that the group by clause was performed before the order by:

The saved.recipe_id column is an integer generated by UNIX_TIMESTAMP()

SELECT
    saved.recipe_id,
    saved.`date`,
    user.user_id
FROM saved
    JOIN user
        ON user.id = saved.user_id
GROUP BY saved.recipe_id
ORDER BY saved.`date` DESC

So I tried all sorts of different possible solution with sub queries and other bs. In the end I ended up with trying out some different sub queries in the join clause witch required me to change the table order from the from clause to the join clause. I decided to just try the following out:

SELECT
    saved.recipe_id,
    saved.`date`,
    user.user_id
FROM user
    JOIN saved
        ON user.id = saved.user_id
GROUP BY saved.recipe_id
ORDER BY saved.`date` DESC

For some reason this seems to order correctly, but why?
How can this change make my query sort more correctly then before?
Does it really? or is it just happen to do it for the test cases I put it up against?

解决方案

So the problem I first had with the following query was that the group by clause was performed before the order by:

This is not a problem. This is how SQL is defined and how it operates. The group by creates a new set of rows and order by orders those rows.

There is no ordering issue here. There is an "understanding of SQL" issue. Your order by is only ordering the results of the query. These results are produced by the group by, and the order o fthe joins has nothing to do with the results.

You are using a MySQL extension called Hidden Columns. This is when you have an aggregation query that has columns in the select (or having or order by clauses) that are not part of aggregation functions (sum(), etc) or part of the group by. Here is a quote from the documentation:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

Presumably, you want the most recent date and user associated with that. The following query does what you want correctly and consistently:

SELECT saved.recipe_id, max(saved.`date`) as MostRecentDate,
       substring_index(group_concat(user.user_id), ',', 1) as MostRecentUser
FROM user JOIN
     saved
     ON user.id = saved.user_id
GROUP BY saved.recipe_id
ORDER BY max(saved.`date`) DESC;

这篇关于MySQL - JOIN，GROUP BY，ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！