与标准SQL的差异
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
正如之前在手册中关于 ORDER BY 子句的部分提到的，jOOQ 稍微改变了这些集合运算符的语义。虽然在 SQL 中，集合操作子查询可能不会立即包含任何 ORDER BY 子句 或 LIMIT 子句（除非您将子查询包装到 派生表 中），但 jOOQ 允许您这样做。为了从数据库中选择最年轻和最年长的作者，您可以使用 jOOQ 发出以下语句（呈现为 MySQL 方言）
(SELECT * FROM AUTHOR ORDER BY DATE_OF_BIRTH ASC LIMIT 1) UNION (SELECT * FROM AUTHOR ORDER BY DATE_OF_BIRTH DESC LIMIT 1) ORDER BY 1
create.selectFrom(AUTHOR)
.orderBy(AUTHOR.DATE_OF_BIRTH.asc()).limit(1)
.union(
selectFrom(AUTHOR)
.orderBy(AUTHOR.DATE_OF_BIRTH.desc()).limit(1))
.orderBy(1)
.fetch();
如果您的数据库不支持排序的 UNION 子查询，则子查询将嵌套在派生表中。
SELECT * FROM ( SELECT * FROM AUTHOR ORDER BY DATE_OF_BIRTH ASC LIMIT 1 ) UNION SELECT * FROM ( SELECT * FROM AUTHOR ORDER BY DATE_OF_BIRTH DESC LIMIT 1 ) ORDER BY 1
方言支持
此示例使用 jOOQ
select(BOOK.ID).from(BOOK).orderBy(BOOK.ID).limit(1).union(select(AUTHOR.ID).from(AUTHOR).orderBy(AUTHOR.ID).limit(1)).orderBy(1)
翻译成以下特定方言的表达式
Access, SQLDataWarehouse, Sybase
( SELECT TOP 1 BOOK.ID FROM BOOK ORDER BY BOOK.ID ) UNION ( SELECT TOP 1 AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID ) ORDER BY 1
ASE, SQLServer
SELECT * FROM ( SELECT TOP 1 BOOK.ID FROM BOOK ORDER BY BOOK.ID ) x UNION SELECT * FROM ( SELECT TOP 1 AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID ) x ORDER BY 1
Aurora MySQL, Aurora Postgres, CockroachDB, DuckDB, Exasol, HSQLDB, Hana, MySQL, Redshift, Snowflake, Vertica, YugabyteDB
( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID LIMIT 1 ) UNION ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID LIMIT 1 ) ORDER BY 1
BigQuery
( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID LIMIT 1 ) UNION DISTINCT ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID LIMIT 1 ) ORDER BY 1
ClickHouse
SELECT
t.*
FROM (
(
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
LIMIT 1
)
UNION DISTINCT (
SELECT AUTHOR.ID
FROM AUTHOR
ORDER BY AUTHOR.ID
LIMIT 1
)
) t
ORDER BY 1
Databricks
( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID LIMIT CAST(1 AS int) ) UNION ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID LIMIT CAST(1 AS int) ) ORDER BY 1
DB2, Firebird, Oracle
SELECT * FROM ( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID FETCH NEXT 1 ROWS ONLY ) x UNION SELECT * FROM ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID FETCH NEXT 1 ROWS ONLY ) x ORDER BY 1
Derby, H2, MariaDB, Postgres, Trino
( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID FETCH NEXT 1 ROWS ONLY ) UNION ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID FETCH NEXT 1 ROWS ONLY ) ORDER BY 1
Informix
SELECT *
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) x
UNION
SELECT *
FROM (
SELECT *
FROM (
SELECT FIRST 1 AUTHOR.ID
FROM AUTHOR
ORDER BY AUTHOR.ID
) x
) x
ORDER BY 1
MemSQL
SELECT
t.*
FROM (
(
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
LIMIT 1
)
UNION (
SELECT AUTHOR.ID
FROM AUTHOR
ORDER BY AUTHOR.ID
LIMIT 1
)
) t
ORDER BY 1
SQLite
SELECT * FROM ( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID LIMIT 1 ) x UNION SELECT * FROM ( SELECT AUTHOR.ID FROM AUTHOR ORDER BY AUTHOR.ID LIMIT 1 ) x ORDER BY 1
Teradata
(
SELECT TOP 1 BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
)
UNION (
SELECT *
FROM (
SELECT TOP 1 AUTHOR.ID
FROM AUTHOR
ORDER BY AUTHOR.ID
) x
)
ORDER BY 1
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听到！