MySQL GROUP BY 只返回第一行

Question

我有一个名为 forms 的表，其结构如下-

GROUP       | FORM       | FILEPATH
====================================
SomeGroup   | SomeForm1  | SomePath1
SomeGroup   | SomeForm2  | SomePath2
------------------------------------

我使用以下查询-

SELECT * FROM forms GROUP BY 'GROUP'

它只返回第一行-

GROUP       | FORM       | FILEPATH
====================================
SomeGroup   | SomeForm1  | SomePath1
------------------------------------

它不应该返回两者(或全部)吗？还是我(可能)错了？

Answer 1

作为the manual状态:

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL because the name column in the select list does not appear in the GROUP BY:

SELECT o.custid, c.name, MAX(o.payment)  FROM orders AS o, customers AS c  WHERE o.custid = c.custid  GROUP BY o.custid;

For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause.

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.

在你的例子中，MySQL 正确地执行了分组操作，但是(因为你选择了所有列，包括你没有对查询进行分组的列)从每个组中给你一个不确定的记录。