使用MySQL进行分组时返回哪一行字段？ [英] Which row's fields are returned when Grouping with MySQL?

问题描述

我有一个MySQL表，其中包含字段 id 和字符串。 id s是唯一的。 string s是varchars并且是非唯一的。

我执行以下查询：

  SELECT id，string，COUNT（*）AS频率
 FROM表
 GROUP BY字符串
 ORDER BY频率DESC ，id ASC 
  

问题

假设表中包含三行，它们具有相同的 string 值，并且 id s 1,2和3

1. 将返回 id （1,2或3）？


2. 其中 id 是这个查询将转到 ORDER BY （与返回的相同？...请参阅问题1）？
   
3. 您可以控制哪些 id 被返回/用于排序？例如。从GROUP返回最大的 id 或第一个 id 。
   

我最终想要做的是获得相同字符串的频率出现率，按该频率排序，从最高到最低以及频率平局，按<$ c $排序c> id 与返回/排序的组中最小的 id 。我让情况变得更加通用，以弄清楚MySQL如何处理这种情况。

（1，2或3）？

A：服务器将选择所有具有相同名称的记录最快获取，这是不可预知的）。引用官方文档：

服务器可以自由选择每个组的任何值，因此除非它们相同，否则选择的值是不确定的。

有关此 link 。

BY（与返回值相同？...参见问题1）？

找出返回数据的顺序是没有意义的因为你无法预测你将要得到的结果。但是，很可能您会根据不可预知的ID列进行排序。

您可以控制返回/用于排序的ID吗？例如。返回最大的ID，或者是一个GROUP的第一个ID。

在这一点上，你应该假设你不能。再次阅读文档。

使事情更加清晰：您无法预测使用不当的GROUP BY子句的结果。 MySQL的主要问题是它允许你以非标准的方式使用它，但你需要知道如何使用该功能。其背后的主要观点是按领域分组，你知道永远都是一样的。 EG：

  SELECT id，name，COUNT（*）AS频率
 FROM表
 GROUP BY id 
  

在这里，您知道 name 将是唯一的作为 id 功能性地确定 name 。所以你知道的结果是有效的。如果你按名称分组，那么这个查询将会更加标准，但在MySQL中的表现会稍微差一些。

最后要注意的是，根据我的经验，结果在那些非标准查询中，对于选定和非分组字段，通常是应用 GROUP BY ，然后是 ORDER BY 在该字段上。这就是为什么很多次似乎的原因。但是，如果你继续测试，你最终会发现这发生在95％的时间。你不能依靠那个号码。

I have a MySQL table with the fields id and string. ids are unique. strings are varchars and are non-unique.

I perform the following query:

SELECT id, string, COUNT( * ) AS frequency
FROM table
GROUP BY string
ORDER BY frequency DESC, id ASC

Questions

Assume the table contains three rows with identical string values, and ids 1, 2, and 3.

1. Which id is going to be returned ( 1, 2, or 3 )?
2. Which id is this query going to ORDER BY ( Same as is returned? ... see question 1 )?
3. Can you control which id is returned / used for ordering? eg. Return the largest id, or the first id from a GROUP.

What I'm ultimately trying to do is get a frequency occurrence for identical strings, order by that frequency, highest to lowest, and on a frequency tie, order by id with the smallest id from the group returned / ordered by. I made the situation more generic to figure out how MySQL handles this situation.

解决方案

Which id is going to be returned ( 1, 2, or 3 )?

A: The server will choose for all the records that have the same name the id it wants (most likely the fastest to fetch, which is unpredictable). To cite the official documentation:

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Much more information in this link.

Which id is this query going to ORDER BY ( Same as is returned? ... see question 1 )?

It makes no sense to find out in what order the data retrieved will be returned as you can't predict the result you are going to get. However, it is very likely that you get the result sorted by the unpredictable ID column.

Can you control which id is returned / used for ordering? eg. Return the largest id, or the first id from a GROUP.

You should be assuming at this point that you can't. Read again the documentation.

Making things even more clear: You can't predict the result of an improperly used GROUP BY clause. The main issue with MySQL is that it allows you to use it in a non-standard way but you need to know how to make use of that feature. The main point behind it is to group by fields that you know will always be the same. EG:

SELECT id, name, COUNT( * ) AS frequency
FROM table
GROUP BY id

Here, you know name will be unique as id functionally determines name. So the result you know is valid. If you grouped also by name this query would be more standard but will perform slightly worse in MySQL.

As a final note, take into account that, in my experience the results in those non-standard queries for the selected and non-grouped fields are usually the ones that you would get applying a GROUP BY and then an ORDER BY on that field. That is why so many times it seems to work. However, if you keep testing you will eventually find out that this happens 95% of the time. And you can not rely on that number.

这篇关于使用MySQL进行分组时返回哪一行字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！