BETWEEN谓词 (degree > 1)
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
SQL BETWEEN 谓词也适用于 行值表达式。 很像 1 度 BETWEEN 谓词，它是根据一对常规比较谓词定义的
A BETWEEN B AND C A BETWEEN SYMMETRIC B AND C
A >= B AND A <= C (A >= B AND A <= C) OR (A >= C AND A <= B)
以上内容可以根据手册中关于行值表达式比较谓词的部分中列出的规则分解出来。
jOOQ 支持 BETWEEN [SYMMETRIC] 谓词，并在必要时在所有 SQL 方言中模拟它。 此处给出一个例子
row(BOOK.ID, BOOK.TITLE).between(1, "A").and(10, "Z");
方言支持
此示例使用 jOOQ
row(BOOK.ID, BOOK.TITLE).between(1, "A").and(10, "Z")
翻译成以下特定方言的表达式
ASE, Access, Derby, DuckDB, Exasol, Firebird, Hana, Informix, MemSQL, Oracle, SQLDataWarehouse, SQLServer, Sybase
(
(
BOOK.ID >= 1
AND (
BOOK.ID > 1
OR (
BOOK.ID = 1
AND BOOK.TITLE >= 'A'
)
)
)
AND (
BOOK.ID <= 10
AND (
BOOK.ID < 10
OR (
BOOK.ID = 10
AND BOOK.TITLE <= 'Z'
)
)
)
)
Aurora MySQL, MariaDB, MySQL
( (BOOK.ID, BOOK.TITLE) >= (1, 'A') AND (BOOK.ID, BOOK.TITLE) <= (10, 'Z') )
Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, H2, HSQLDB, Postgres, Redshift, SQLite, Snowflake, Trino, Vertica, YugabyteDB
(BOOK.ID, BOOK.TITLE) BETWEEN (1, 'A') AND (10, 'Z')
Databricks
( coalesce(BOOK.ID), coalesce(BOOK.TITLE) ) BETWEEN (1, 'A') AND (10, 'Z')
Teradata
(
(BOOK.ID, BOOK.TITLE) >= (
SELECT 1, 'A'
FROM (
SELECT 1 AS "dual"
) AS "dual"
)
AND (BOOK.ID, BOOK.TITLE) <= (
SELECT 10, 'Z'
FROM (
SELECT 1 AS "dual"
) AS "dual"
)
)
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！