QUALIFY子句
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
一些精选的方言支持非常有用的 QUALIFY 子句，它可以用于在使用窗口函数时进行过滤，而无需在派生表中嵌套窗口函数计算。
例如，如果您无权访问 WITH TIES 子句，您可以像这样轻松地模拟它。以下查询查找前 5 名作者WITH TIES，并计算他们的书籍
SELECT AUTHOR_ID, count(*) FROM BOOK GROUP BY AUTHOR_ID QUALIFY rank() OVER (ORDER BY count(*) DESC) <= 5 ORDER BY count(*) DESC
create.select(BOOK.AUTHOR_ID, count())
.from(BOOK)
.groupBy(BOOK.AUTHOR_ID)
.qualify(rank().over(orderBy(count().desc())).le(5))
.orderBy(count().desc())
.fetch();
如果您的方言本身不支持 QUALIFY，则 jOOQ 可以应用从 QUALIFY 到派生表的转换。
方言支持
此示例使用 jOOQ
select(AUTHOR.ID).from(AUTHOR).qualify(rank().over(orderBy(AUTHOR.ID)).le(10))
翻译成以下特定方言的表达式
Aurora Postgres、CockroachDB、MemSQL、Postgres、SQLite、Trino、Vertica、YugabyteDB
SELECT t.ID ID
FROM (
SELECT
*,
(rank() OVER (ORDER BY AUTHOR.ID) <= 10) w0
FROM AUTHOR
) t
WHERE w0
BigQuery
SELECT AUTHOR.ID FROM AUTHOR WHERE TRUE QUALIFY rank() OVER (ORDER BY AUTHOR.ID) <= 10
ClickHouse、Databricks、DuckDB、Exasol、H2、Snowflake、Teradata
SELECT AUTHOR.ID FROM AUTHOR QUALIFY rank() OVER (ORDER BY AUTHOR.ID) <= 10
DB2、SQLDataWarehouse、SQLServer、Sybase
SELECT t.ID ID
FROM (
SELECT
*,
CASE
WHEN rank() OVER (ORDER BY AUTHOR.ID) <= 10 THEN 1
WHEN NOT (rank() OVER (ORDER BY AUTHOR.ID) <= 10) THEN 0
END w0
FROM AUTHOR
) t
WHERE w0 = 1
Firebird
SELECT t.ID ID
FROM (
SELECT
AUTHOR.ID,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
AUTHOR.DATE_OF_BIRTH,
AUTHOR.YEAR_OF_BIRTH,
AUTHOR.DISTINGUISHED,
CASE
WHEN rank() OVER (ORDER BY AUTHOR.ID) <= 10 THEN 1
WHEN NOT (rank() OVER (ORDER BY AUTHOR.ID) <= 10) THEN 0
END w0
FROM AUTHOR
) t
WHERE w0 = 1
Hana
SELECT t.ID ID
FROM (
SELECT
*,
CASE
WHEN rank() OVER (ORDER BY AUTHOR.ID) <= 10 THEN TRUE
WHEN NOT (rank() OVER (ORDER BY AUTHOR.ID) <= 10) THEN FALSE
END w0
FROM AUTHOR
) t
WHERE w0 = TRUE
Informix
SELECT t.ID ID
FROM (
SELECT
*,
CASE
WHEN rank() OVER (ORDER BY AUTHOR.ID) <= 10 THEN CAST('t' AS boolean)
WHEN NOT (rank() OVER (ORDER BY AUTHOR.ID) <= 10) THEN CAST('f' AS boolean)
END w0
FROM AUTHOR
) t
WHERE w0
MariaDB、MySQL、Oracle
SELECT t.ID ID
FROM (
SELECT
AUTHOR.ID,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
AUTHOR.DATE_OF_BIRTH,
AUTHOR.YEAR_OF_BIRTH,
AUTHOR.DISTINGUISHED,
(rank() OVER (ORDER BY AUTHOR.ID) <= 10) w0
FROM AUTHOR
) t
WHERE w0
Redshift
SELECT AUTHOR.ID FROM AUTHOR WHERE 1 = 1 QUALIFY rank() OVER (ORDER BY AUTHOR.ID) <= 10
ASE、Access、Aurora MySQL、Derby、HSQLDB
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！