MySQL GROUP BY行为 [英] MySQL GROUP BY behavior

问题描述

给出以下表格 foo '

  ID |名字|姓氏
 ---------------------------- 
 67 John Smith 
 ----- ----------------------- 
 67 Bill Jacobs 
  

什么 first_name 和 last_name 将会返回以下查询以及为什么？

  SELECT * FROM foo WHERE ID = 67 GROUP BY ID 
  

解决方案

MySQL随意选择一行。在实践中，通常使用的MySQL存储引擎将返回组中的 first 行的值，即物理存储。

  create table foo（id serial primary key，category varchar（10））; 
 
插入foo（类别）值
（'foo'），（'foo'），（'foo'），（'bar'），（'bar'），（ '酒吧'）; 
 
按类别从foo组中选择*; 
 
 + ---- + ---------- + 
 | id |类别| 
 + ---- + ---------- + 
 | 4 | bar | 
 | 1 | foo | 
 + ---- + ---------- + 
  

其他人是正确的，MySQL允许你运行这个查询，即使它有任意的和潜在的误导结果。 SQL标准和大多数其他RDBMS供应商不允许使用这种不明确的GROUP BY查询。这称为单值规则：选择列表中的所有列必须显式地属于GROUP BY条件的一部分，否则在集合函数内部，例如 COUNT（）， MAX（）等。

MySQL支持SQL模式 ONLY_FULL_GROUP_BY ，如果您尝试运行违反SQL标准语义的查询，则MySQL会返回错误。

AFAIK，SQLite是唯一一个在分组查询中允许模糊列的其他RDBMS。 SQLite返回组中的 last 行的值：

  select * from foo group by category; 
 
 6 | bar 
 3 | foo 
  

---

我们可以想象这样的查询不会含糊不清，但仍然违反了SQL标准语义。

  SELECT foo。*，parent_of_foo。* 
 FROM foo JOIN parent_of_foo 
 ON（foo.parent_id = parent_of_foo.parent_id）
 GROUP BY foo_id; 
  

没有合理的方法可能产生不明确的结果。如果我们GROUP BY foo的主键，foo中的每一行都会获得它自己的组。因此，foo中的任何列在组中只能有一个值。即使加入到foo中的外键引用的另一个表中，如果组由foo的主键定义，则每个组中只能有一个值。

MySQL和SQLite相信你设计逻辑上明确的查询。形式上，选择列表中的每一列必须是GROUP BY条件中列的功能依赖关系。如果你不坚持这一点，那是你的错。 ： - ）

标准SQL更严格，不允许某些查询可以明确 - 可能是因为它对于RDBMS来说太复杂一般来说确实如此。

Given the following table 'foo'

ID | First Name | Last Name
----------------------------
67   John        Smith
----------------------------
67   Bill        Jacobs

What first_name and last_name will the following query return and why?

SELECT * FROM foo WHERE ID = 67 GROUP BY ID

解决方案

MySQL chooses a row arbitrarily. In practice, commonly used MySQL storage engines return the values from the first row in the group, with respect to the physical storage.

create table foo (id serial primary key, category varchar(10));

insert into foo (category) values 
  ('foo'), ('foo'), ('foo'), ('bar'), ('bar'), ('bar');

select * from foo group by category;

+----+----------+
| id | category |
+----+----------+
|  4 | bar      |
|  1 | foo      |
+----+----------+

Other folks are correct that MySQL allows you to run this query even though it has arbitrary and potentially misleading results. The SQL standard, and most other RDBMS vendors, disallow this kind of ambiguous GROUP BY query. This is called the Single-Value Rule: all columns in the select-list must be explicitly part of the GROUP BY criteria, or else inside an aggregate function, e.g. COUNT(), MAX(), etc.

MySQL supports a SQL mode ONLY_FULL_GROUP_BY that makes MySQL return an error if you try to run a query that violates SQL standard semantics.

AFAIK, SQLite is the only other RDBMS that allows ambiguous columns in a grouped query. SQLite returns values from the last row in the group:

select * from foo group by category;

6|bar
3|foo

---

We can imagine queries that would not be ambiguous, yet still violate the SQL standard semantics.

SELECT foo.*, parent_of_foo.* 
FROM foo JOIN parent_of_foo 
  ON (foo.parent_id = parent_of_foo.parent_id) 
GROUP BY foo_id;

There's no logical way this could produce ambiguous results. Each row in foo gets its own group, if we GROUP BY the primary key of foo. So any column from foo can have only one value in the group. Even joining to another table referenced by a foreign key in foo can have only one value per group, if the groups are defined by the primary key of foo.

MySQL and SQLite trust you to design logically unambiguous queries. Formally, every column in the select-list must be a functional dependency of the columns in the GROUP BY criteria. If you don't adhere to this, it's your fault. :-)

Standard SQL is more strict and disallows some queries that could be unambiguous--probably because it would be too complex for the RDBMS to be sure in general.

这篇关于MySQL GROUP BY行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！