DECODE
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
一些 SQL 方言，包括 Db2、H2、Oracle，知道一个更简洁但可能不太易读的 DECODE() 函数，该函数具有可变数量的参数。此函数的工作方式类似于 NULL 安全的 CASE 表达式。jOOQ 支持 DECODE() 函数，并在所有不支持原生支持的方言中使用 CASE 表达式模拟它
SELECT
-- Oracle:
DECODE(FIRST_NAME, 'Paulo', 'brazilian',
'George', 'english',
'unknown'),
-- Other SQL dialects
CASE
WHEN FIRST_NAME IS NOT DISTINCT FROM 'Paulo' THEN 'brazilian'
WHEN FIRST_NAME IS NOT DISTINCT FROM 'George' THEN 'english'
ELSE 'unknown'
END
FROM AUTHOR
// Use the Oracle-style DECODE() function with jOOQ. // Note, that you will not be able to rely on type-safety decode( AUTHOR.FIRST_NAME, "Paulo", "brazilian", "George", "english", "unknown" );
decode(AUTHOR.FIRST_NAME, "Paulo", "BR", "George", "EN", "unknown")
翻译成以下特定方言的表达式
ASE, SQLDataWarehouse
CASE
WHEN EXISTS (
SELECT AUTHOR.FIRST_NAME x
INTERSECT
SELECT 'Paulo' x
) THEN 'BR'
WHEN EXISTS (
SELECT AUTHOR.FIRST_NAME x
INTERSECT
SELECT 'George' x
) THEN 'EN'
ELSE 'unknown'
END
Aurora MySQL, MySQL
CASE WHEN (AUTHOR.FIRST_NAME <=> 'Paulo') THEN 'BR' WHEN (AUTHOR.FIRST_NAME <=> 'George') THEN 'EN' ELSE 'unknown' END
Aurora Postgres, BigQuery, CockroachDB, DuckDB, Firebird, HSQLDB, Postgres, SQLServer, Snowflake, Trino, YugabyteDB
CASE WHEN AUTHOR.FIRST_NAME IS NOT DISTINCT FROM 'Paulo' THEN 'BR' WHEN AUTHOR.FIRST_NAME IS NOT DISTINCT FROM 'George' THEN 'EN' ELSE 'unknown' END
ClickHouse
CASE WHEN arrayUniq(ARRAY(AUTHOR.FIRST_NAME, 'Paulo')) = 1 THEN 'BR' WHEN arrayUniq(ARRAY(AUTHOR.FIRST_NAME, 'George')) = 1 THEN 'EN' ELSE 'unknown' END
DB2, Databricks, Exasol, H2, Informix, MemSQL, Oracle, Redshift, Teradata, Vertica
decode( AUTHOR.FIRST_NAME, 'Paulo', 'BR', 'George', 'EN', 'unknown' )
Derby
CASE
WHEN EXISTS (
SELECT AUTHOR.FIRST_NAME x
FROM SYSIBM.SYSDUMMY1
INTERSECT
SELECT 'Paulo' x
FROM SYSIBM.SYSDUMMY1
) THEN 'BR'
WHEN EXISTS (
SELECT AUTHOR.FIRST_NAME x
FROM SYSIBM.SYSDUMMY1
INTERSECT
SELECT 'George' x
FROM SYSIBM.SYSDUMMY1
) THEN 'EN'
ELSE 'unknown'
END
Hana
map( AUTHOR.FIRST_NAME, 'Paulo', 'BR', 'George', 'EN', 'unknown' )
MariaDB
decode_oracle( AUTHOR.FIRST_NAME, 'Paulo', 'BR', 'George', 'EN', 'unknown' )
SQLite
CASE WHEN (AUTHOR.FIRST_NAME IS 'Paulo') THEN 'BR' WHEN (AUTHOR.FIRST_NAME IS 'George') THEN 'EN' ELSE 'unknown' END
Sybase
CASE
WHEN EXISTS (
SELECT AUTHOR.FIRST_NAME x
FROM SYS.DUMMY
INTERSECT
SELECT 'Paulo' x
FROM SYS.DUMMY
) THEN 'BR'
WHEN EXISTS (
SELECT AUTHOR.FIRST_NAME x
FROM SYS.DUMMY
INTERSECT
SELECT 'George' x
FROM SYS.DUMMY
) THEN 'EN'
ELSE 'unknown'
END
访问
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听到您的意见！