DISTINCT谓词 (degree > 1)
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
对于大于 1 度的行值表达式，也支持DISTINCT 谓词。如果您的数据库不支持行值表达式比较谓词，jOOQ 将按照 SQL 标准中定义的对其进行模拟
(FIRST_NAME, LAST_NAME) IS DISTINCT FROM ('John', 'Doe')
(FIRST_NAME, LAST_NAME) IS NOT DISTINCT FROM ('John', 'Doe')
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).isNotDistinctFrom("John", "Doe")
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).isNotDistinctFrom("John", "Doe")
方言支持
此示例使用 jOOQ
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).isNotDistinctFrom("John", "Doe")
翻译成以下特定方言的表达式
ASE, Exasol, Oracle, SQLDataWarehouse, SQLServer, Vertica
EXISTS ( SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME INTERSECT SELECT 'John', 'Doe' )
Aurora MySQL, MariaDB, MySQL
(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) <=> ('John', 'Doe')
Aurora Postgres, BigQuery, CockroachDB, DuckDB, H2, HSQLDB, Postgres, Snowflake, Trino, YugabyteDB
(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) is not distinct from ('John', 'Doe')
ClickHouse
arrayUniq(ARRAY(
TUPLE (AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME),
TUPLE ('John', 'Doe')
)) = 1
Databricks
(
coalesce(AUTHOR.FIRST_NAME),
coalesce(AUTHOR.LAST_NAME)
) is not distinct from ('John', 'Doe')
DB2
EXISTS ( SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME FROM SYSIBM.DUAL INTERSECT SELECT 'John', 'Doe' FROM SYSIBM.DUAL )
Derby
EXISTS ( SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME FROM SYSIBM.SYSDUMMY1 INTERSECT SELECT 'John', 'Doe' FROM SYSIBM.SYSDUMMY1 )
Firebird
NOT EXISTS ( SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME FROM RDB$DATABASE UNION SELECT 'John', 'Doe' FROM RDB$DATABASE OFFSET 1 ROWS )
Hana, Sybase
EXISTS ( SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME FROM SYS.DUMMY INTERSECT SELECT 'John', 'Doe' FROM SYS.DUMMY )
Informix
EXISTS (
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
FROM (
SELECT 1 AS dual
FROM systables
WHERE (tabid = 1)
) AS dual
INTERSECT
SELECT 'John', 'Doe'
FROM (
SELECT 1 AS dual
FROM systables
WHERE (tabid = 1)
) AS dual
)
MemSQL
EXISTS ( SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME FROM DUAL INTERSECT SELECT 'John', 'Doe' FROM DUAL )
Redshift
NOT ((AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) is distinct from ('John', 'Doe'))
SQLite
(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) IS ('John', 'Doe')
Teradata
EXISTS (
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
FROM (
SELECT 1 AS "dual"
) AS "dual"
INTERSECT
SELECT 'John', 'Doe'
FROM (
SELECT 1 AS "dual"
) AS "dual"
)
访问
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？我们很乐意听取您的意见！