GROUP BY DESC如何选择其订单? [英] How does GROUP BY DESC select its order?

问题描述

因此，我正在为商店创建部分.存储可以具有多个作用域，如果没有为给定的store_id设置section_identifier，则应回退到全局存储0.

So I am creating sections for a store. The store can have multiple scopes, if there isn't a section_identifier set for a given store_id it should fallback to the global store which is 0.

我想要的SQL命令应该为任何相关的给定商店返回section_options的列表.

The SQL command that I want should return a list of section_options for any related given store.

我的桌子的例子:

SELECT * FROM my_table:

+----+--------------------+----------------------+----------+
| id | section_identifier | option_identifier    | store_id |
+----+--------------------+----------------------+----------+
| 17 | header             | header_option_one    |        1 |
| 18 | footer             | footer_option_one    |        0 |
| 19 | homepage_feature   | homepage_feature_one |        0 |
| 23 | header             | header_option_three  |        0 |
| 25 | homepage_feature   | homepage_feature_one |        1 |
+----+--------------------+----------------------+----------+

所以section_identifier是唯一的，我需要为商店1返回的ID是17、18和25.

So section_identifier is unique, the IDs I need back for store 1 would be 17, 18 and 25.

当我跑步时:

SELECT * FROM my_table GROUP BY section_identifier它返回:

+----+--------------------+----------------------+----------+
| id | section_identifier | option_identifier    | store_id |
+----+--------------------+----------------------+----------+
| 18 | footer             | footer_option_one    |        0 |
| 23 | header             | header_option_three  |        0 |
| 19 | homepage_feature   | homepage_feature_one |        0 |
+----+--------------------+----------------------+----------+

这意味着如果我运行SELECT * FROM my_table GROUP BY section_identifier DESC:

我得到响应(这是我想要的输出):

I get the response (this is my desired output):

+----+--------------------+----------------------+----------+
| id | section_identifier | option_identifier    | store_id |
+----+--------------------+----------------------+----------+
| 25 | homepage_feature   | homepage_feature_one |        1 |
| 17 | header             | header_option_one    |        1 |
| 18 | footer             | footer_option_one    |        0 |
+----+--------------------+----------------------+----------+

尽管这可行，但我对原因却一无所知.

Although this works, I have no understanding of as to why.

我的理解是，最初的GROUP BY应该获得数据库中的第一个实例，即IE，我期望的响应应该是:

Its my understanding the initial GROUP BY should get the first instance in the database, IE the response I expect should be:

+----+--------------------+----------------------+----------+
| id | section_identifier | option_identifier    | store_id |
+----+--------------------+----------------------+----------+
| 18 | footer             | footer_option_one    |        0 |
| 17 | header             | header_option_three  |        1 |
| 19 | homepage_feature   | homepage_feature_one |        0 |
+----+--------------------+----------------------+----------+

但是，似乎以某种方式引用了我的store_id?我尝试了几种不同的组合，但每次我都怪异地获得了预期的结果，但我对原因却一无所知.

However, it seems to be referencing my store_id somehow? I have tried a few different combinations and Im weirdly getting my expected result each time but I have no understanding as to why.

有人可以向我解释吗?

PS

我尝试更新id = 7的option_identifier来查看MySql是否引用了磁盘上最新保存的内容，并且没有改变结果.

I have tried updating the option_identifier of id = 7 to see if MySql references the latest saved on disk and it didn't change the result.

也:我不是打算使用此功能，也不打算寻求替代方案，而是想问问这是怎么回事?

Also: I'm not planning on using this feature or asking for an alternative, I'm asking what's going on with it?

推荐答案

SELECT * FROM my_table GROUP BY section_identifier

是无效的SQL查询.

让我们获取上面的查询，并查看GROUP BY的工作方式.首先，数据库引擎选择与WHERE子句匹配的所有行.此查询中没有WHERE子句；这意味着表的所有行都用于生成结果集.

Let's get the query above and see how GROUP BY works. First the database engine selects all the rows that match the WHERE clause. There is no WHERE clause in this query; this means all the rows of the table are used to generate the result set.

然后使用GROUP BY子句中指定的表达式对行进行分组:

It then groups the rows using the expressions specified in the GROUP BY clause:

+----+--------------------+----------------------+----------+
| id | section_identifier | option_identifier    | store_id |
+----+--------------------+----------------------+----------+
| 17 | header             | header_option_one    |        1 |
| 23 | header             | header_option_three  |        0 |
+----+--------------------+----------------------+----------+
| 18 | footer             | footer_option_one    |        0 |
+----+--------------------+----------------------+----------+
| 19 | homepage_feature   | homepage_feature_one |        0 |
| 25 | homepage_feature   | homepage_feature_one |        1 |
+----+--------------------+----------------------+----------+

我在上面的清单中标记了各个组，以使所有内容都清晰明了.

I marked the groups in the listing above to make everything clear.

下一步，数据库引擎将从每个组中产生单行.但是如何?

On the next step, from each group the database engine produces a single row. But how?

查询的SELECT子句为SELECT *. *代表表列的完整列表；在这种情况下，SELECT *是写的简短方式:

The SELECT clause of your query is SELECT *. * stands for the full list of table columns; in this case, SELECT * is a short way to write:

SELECT id, section_identifier, option_identifier, store_id

让我们分析第一组的列id的值.数据库引擎应为id选择什么值? 17或23?为什么选择17，为什么选择23?

Let's analyze the values of column id for the first group. What value should the database engine choose for id? 17 or 23? Why 17 and why 23?

没有任何条件使17胜于23.它只是选择其中之一(可能是17，但这取决于很多内部因素)，然后选择其中一个.

It does not have any criteria to favor 17 over 23. It just picks one of them (probably 17 but this depends on a lot of internal factors) and goes one.

确定section_identifier的值没有问题.这是用于GROUP BY的列，它在组中的所有值都相等.

There is no problem to determine the value for section_identifier. It is the column used to GROUP BY, all its values in a group are equal.

选择困境再次出现在列option_identifier和store_id上.

The choosing dilemma occurs again on columns option_identifier and store_id.

根据标准SQL，您的查询无效，因此无法执行.但是，某些数据库引擎如上所述运行它.不是的表达式的值(至少以下之一):

According to the standard SQL your query is not valid and it cannot be executed. However, some database engines run it as described above. The values for expressions that are not (at least one of the below):

• 用于GROUP BY子句；
• 与 GROUP BY聚合函数一起使用SELECT子句中的a>；
• 在功能上依赖于GROUP BY子句中使用的列；

• used in the GROUP BY clause;
• used with GROUP BY aggregate functions in the SELECT clause;
• functionally dependent of columns used in the GROUP BY clause;

不确定.

从版本5.7.5开始，MySQL实现功能依赖检测，默认情况下，它会拒绝无效的GROUP BY查询，如您的查询.

Since version 5.7.5, MySQL implements functional dependency detection and, by default, it rejects an invalid GROUP BY query like yours.

我不清楚您要如何获得结果集.无论如何，如果要从表中获取一些行，则GROUP BY 不是正确的方法. GROUP BY 不从表中选择行，它使用表中的值生成新值.大多数情况下，GROUP BY生成的行与源表中的任何行都不匹配.

It's not clear for me how you want to get the result set. Anyway, if you want to get some rows from the table then GROUP BY is not the correct way to do it. GROUP BY does not select rows from a table, it generates new values using the values from the table. A row generated by GROUP BY, most of the times, do not match any row from the source table.

您可以在此答案中找到可能的解决方案.阅读并理解该主意之后，您将必须自己编写查询(并且很清楚应该如何选择优胜者"行).

You can find a possible solution to your problem in this answer. You'll have to write the query yourself after you read and understand the idea (and is very clear to you how the "winner" rows should be selected).

这篇关于GROUP BY DESC如何选择其订单?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！