VALUES()表构造函数
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
一些数据库允许使用 VALUES() 构造函数来表达内存中的临时表。 此构造函数的工作方式通常与 INSERT 语句或 MERGE 语句中已知的 VALUES() 子句相同。 使用 jOOQ，您还可以使用 VALUES() 表构造函数来创建可在 SELECT 语句的 FROM 子句中使用的表
SELECT a, b
FROM VALUES(1, 'a'),
(2, 'b') t(a, b)
create.select()
.from(values(row(1, "a"),
row(2, "b")).as("t", "a", "b"))
.fetch();
selectFrom(values(row(1, "a"), row(2, "b")).as("t", "a", "b"))
翻译成以下特定方言的表达式
访问
SELECT t.a, t.b
FROM (
SELECT
t.*
FROM (
SELECT
1 a,
'a' b
FROM (
SELECT count(*) dual
FROM MSysResources
) AS dual
UNION ALL
SELECT 2, 'b'
FROM (
SELECT count(*) dual
FROM MSysResources
) AS dual
) t
) t
ASE, Redshift, SQLDataWarehouse, Vertica
SELECT t.a, t.b FROM ( SELECT 1, 'a' UNION ALL SELECT 2, 'b' ) t (a, b)
Aurora MySQL, MemSQL
SELECT t.a, t.b
FROM (
SELECT
t.*
FROM (
SELECT
1 a,
'a' b
FROM DUAL
UNION ALL
SELECT 2, 'b'
FROM DUAL
) t
) t
Aurora Postgres, CockroachDB, DB2, Databricks, Derby, DuckDB, Exasol, H2, HSQLDB, Oracle, Postgres, SQLServer, Snowflake, Trino, YugabyteDB
SELECT t.a, t.b
FROM (
VALUES
(1, 'a'),
(2, 'b')
) t (a, b)
BigQuery
SELECT t.a, t.b
FROM (
SELECT
null a,
null b
FROM UNNEST([STRUCT(1 AS dual)]) AS dual
WHERE FALSE
UNION ALL
SELECT *
FROM UNNEST ([
STRUCT (1, 'a'),
STRUCT (2, 'b')
]) t
) t
ClickHouse, MariaDB
SELECT t.a, t.b
FROM (
SELECT
t.*
FROM (
SELECT
1 a,
'a' b
UNION ALL
SELECT 2, 'b'
) t
) t
Firebird
SELECT t.a, t.b FROM ( SELECT 1, 'a' FROM RDB$DATABASE UNION ALL SELECT 2, 'b' FROM RDB$DATABASE ) t (a, b)
Hana
SELECT t.a, t.b
FROM (
SELECT
t.*
FROM (
SELECT
1 a,
'a' b
FROM SYS.DUMMY
UNION ALL
SELECT 2, 'b'
FROM SYS.DUMMY
) t
) t
Informix
SELECT t.a, t.b
FROM (
TABLE (MULTISET {
ROW (1, 'a'),
ROW (2, 'b')
})
) t (a, b)
MySQL
SELECT t.a, t.b
FROM (
VALUES
ROW (1, 'a'),
ROW (2, 'b')
) t (a, b)
SQLite
SELECT t.a, t.b
FROM (
SELECT
null a,
null b
WHERE 1 = 0
UNION ALL
SELECT *
FROM (
VALUES
(1, 'a'),
(2, 'b')
) t
) t
Sybase
SELECT t.a, t.b FROM ( SELECT 1, 'a' FROM SYS.DUMMY UNION ALL SELECT 2, 'b' FROM SYS.DUMMY ) t (a, b)
Teradata
SELECT t.a, t.b
FROM (
SELECT 1, 'a'
FROM (
SELECT 1 AS "dual"
) AS "dual"
UNION ALL
SELECT 2, 'b'
FROM (
SELECT 1 AS "dual"
) AS "dual"
) t (a, b)
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！