为什么带组的SQL查询生成更多的行?

I have the following table:

我有以下表格:

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

When I run the following query:

当我运行以下查询时:

SELECT id, MAX(rev) maxrev, content
FROM YourTable
GROUP BY id;

I get:

我得到:

+------+----------+--------------------------------------+
| id   | maxrev   | content                              |
+------+----------+--------------------------------------+
| 1    |    3     | ...                                  |
| 2    |    1     | ...                                  |
+------+----------+--------------------------------------+

But if I remove the GROUP BY clause as follows:

但如果我按以下条款删除组:

SELECT id, MAX(rev) maxrev, content
FROM YourTable;

I get:

我得到:

+------+----------+--------------------------------------+
| id   | maxrev   | content                              |
+------+----------+--------------------------------------+
| 1    |    3     | ...                                  |
+------+----------+--------------------------------------+

This is counter-intuitive to me because of the expectation that a GROUP BY would reduce the number of results by eliminating duplicate values. However, in the above case, introduction of the GROUP BY does the opposite. Is this because of the MAX() function, and if so, how?

这与我的直觉相反，因为我期望一个组通过消除重复值来减少结果的数量。然而，在上述情况下，团队的引入却恰恰相反。这是因为MAX()函数吗?

PS: The table is based on the SO question here: SQL select only rows with max value on a column. I was trying to understand the answer to that question, and in the process, came across the above situation.

PS:表基于这里的SO问题:SQL只选择列上值最大的行。我试图理解这个问题的答案，在这个过程中，我遇到了上面的情况。

EDIT:

编辑:

I got the above results on sqlfiddle.com using its MySQL 5.6 engine, with no customization/configuration.

我使用MySQL 5.6引擎在sqlfiddle.com上获得了上面的结果，没有定制/配置。

3 个解决方案

#1

It is utilizing your MAX() function dependent on your GROUP BY clause. So, for your first query, you are saying: Give me the maximum rev for each id, whereas the second is just saying Give me the maximum rev in general.

它利用了依赖于GROUP BY子句的MAX()函数。所以，对于你的第一个问题，你说:给我每个id的最大转速，而第二个问题只是告诉我一般的最大转速。

Thanks to xQbert:

由于xQbert:

This does NOT mean that you are getting the row with the max rev in the latter case. It will take values from anywhere in the selection to use for your id and content fields.

这并不意味着在后一种情况下你得到的是带有最大转速的行。它将从选择的任何地方获取值，用于您的id和内容字段。

You can read more about how SQL handles the GROUP BY statement here: Documentation

您可以在这里阅读更多关于SQL如何按语句处理组的内容:文档

#2

This because you are using a version previuos that mysql 5.7 ..these version allow the use of aggregated d function and select column not in group by ... this produce impredicatble result for the not aggregated column .. in mysql 5.7 this beahvior is not allowed ... you have an error if you in select not aggregated function not mentioned in group by

这是因为您使用的是mysql 5.7版本的previuos。这些版本允许使用聚合d函数并选择不属于group by…这对未聚合列产生不可预测的结果。在mysql 5.7中，不允许使用beahvior…如果选择了group by中没有提到的未聚合函数，就会出现错误

the correct sintax is obviuosly the first

正确的国税是第一个。

SELECT id, MAX(rev) maxrev, content
FROM YourTable
GROUP BY id;

#3

SELECT id, MAX(rev) maxrev, content FROM YourTable
GROUP BY id;

When you run this, as there are 2 distinct ids in the table you get two rows in the result, one per id with the max value. The grouping happens on the id column.

当你运行这个时，因为表中有两个不同的id你会得到两个行，每个id一个最大值。分组发生在id列上。

SELECT id, MAX(rev) maxrev, content
FROM YourTable;

If you remove the group by clause, you only get one row in the result corresponding to the max value in the entire table. There is no grouping by id.

如果删除group by子句，则结果中只有一行对应于整个表中的最大值。没有按id分组。

#1