SQL标准知道一个风趣的特性,你可以投射在GROUP BY
子句中列出的主键(或唯一键)的任何功用依靠,而不需求明确地将该功用依靠添加到GROUP BY
子句中。
这意味着什么呢?考虑一下这个简略的形式:
CREATE TABLE author (
id INT NOT NULL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE book (
id INT NOT NULL PRIMARY KEY,
author_id INT NOT NULL REFERENCES author,
title TEXT NOT NULL
);
为了按作者核算书本的数量,咱们倾向于写:
SELECT a.name, count(b.id)
FROM author a
LEFT JOIN book b ON a.id = b.author_id
GROUP BY
a.id, -- Required, because names aren't unique
a.name -- Required in some dialects, but not in others
在这种状况下,咱们有必要通过一些共同的东西来分组,由于假如两个作者都叫John Doe,咱们仍然希望他们能产生不同的组。因而,GROUP BY a.id
是一个必然的成果。
咱们习惯于同时使用GROUP BY a.name
,特别是在这些需求这样的方言中,由于咱们在SELECT
子句中列出了a.name
:
- Db2
- Derby
- Exasol
- Firebird
- HANA
- Informix
- Oracle
- SQL Server
但这真的是有必要的吗?根据SQL标准,它不是,由于在author.id
和author.name
之间存在着功用依靠。换句话说,对于author.id
的每一个值,恰好有一个author.name
的或许值,或者说author.name
是一个函数。author.id
这意味着,假如咱们GROUP BY
两列,或者仅是主键,这并不重要。两种状况下的成果有必要是相同的,因而这是有或许的:
SELECT a.name, count(b.id)
FROM author a
LEFT JOIN book b ON a.id = b.author_id
GROUP BY a.id
哪些SQL方言支撑这个?
至少有以下SQL方言支撑这个言语特性:
- CockroachDB
- H2
- HSQLDB
- MariaDB
- MySQL
- PostgreSQL
- SQLite
- Yugabyte
值得注意的是,在有GROUP BY
的状况下,MySQL从前简略地忽略了一个列是否可以被明确地预测。尽管下面的查询在大多数方言中被拒绝,但在MySQL中,在引进ONLY_FULL_GROUP_BY形式之前,它没有被拒绝:
SELECT author_id, title, count(*)
FROM author
GROUP BY author_id
假如一个作者写了不止一本书,咱们应该为author.title
,显示什么?这没有意义,但MySQL仍然从前答应它,并且会从组中投射任何恣意的值。
今天,MySQL只答应投射与GROUP BY
子句有功用关系的列,这是SQL标准所答应的。
长处和缺陷
尽管防止额定列的较短语法或许更简单保护(假如需求的话,很简单投射额定的列),但在生产中存在一些查询中止的危险,即当根底束缚被禁用时,例如为了迁移。尽管不太或许在一个实时体系中禁用主键,但仍有或许出现这种状况,假如没有主键,以前有用的查询将不再有用,原因与MySQL的旧解释无效相同。不再有功用依靠性的确保。
其他语法
从jOOQ 3.16和#11834开始,将有或许在GROUP BY
子句中直接引用表,而不是单个列。比如说:
SELECT a.name, count(b.id)
FROM author a
LEFT JOIN book b ON a.id = b.author_id
GROUP BY a
语义将是:
- 假如表有一个主键(不管是否复合),在
GROUP BY
子句中使用该主键。 - 假如表没有主键,则列出该表的一切列。
由于jOOQ支撑的RDBMS现在都不支撑这种语法,所以它是一个纯粹的 合成jOOQ功用。