加入，分组，订购 [英] JOIN, GROUP BY, ORDER BY

问题描述

我首先遇到以下查询的问题是group by子句是在order by之前执行的:

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

saved.recipe_id列是由UNIX_TIMESTAMP()

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

因此，我尝试了带有子查询和其他bs的各种不同可能的解决方案.最后，我在join子句中尝试了一些不同的子查询，女巫要求我将表顺序从from子句更改为join子句.我决定尝试以下方法:

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?

推荐答案

所以我首先遇到以下查询的问题是该组 by子句的执行顺序为:

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

这不是问题.这就是SQL的定义方式及其操作方式. group by创建一组新的行，然后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.

这里没有订购问题.有一个"SQL理解"问题.您的order by仅对查询结果进行排序.这些结果由group by产生，并且联接的顺序与结果无关.

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.

您正在使用一个称为隐藏列"的MySQL扩展.这是当您有一个聚合查询，该查询的select(或having或order by子句)中的列不是聚合函数(sum()等)的一部分，也不是group by的一部分.以下是文档的引文:

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扩展了GROUP BY的使用，以便选择列表可以引用 未在GROUP BY子句中命名的非聚合列.这表示 前面的查询在MySQL中是合法的.您可以使用此功能 通过避免不必要的列排序和获得更好的性能 分组.但是，这主要是有用的，当每个中的所有值 在GROUP BY中未命名的非聚合列对于每个列均相同 团体.服务器可以从每个组中自由选择任何值，因此 除非它们相同，否则选择的值是不确定的. 此外，不能从每个组中选择值 受添加ORDER BY子句影响.结果集排序 在选择值之后发生，并且ORDER BY不会影响 服务器在每个组中选择哪个值.

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;

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