WITH ORDINALITY
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
SQL 标准指定了一个 WITH ORDINALITY 子句，可以附加到任何 UNNEST 函数调用（也称为集合派生表）。 PostgreSQL 更进一步，允许将该语法与任何类型的表值函数一起使用，因为在他们的实现中，UNNEST 只是另一种表值函数。 另一方面，CockroachDB 允许将 WITH ORDINALITY 与几乎任何表表达式一起使用，这对于派生表（如果它们是有序的）或 VALUES() 表构造器是有意义的。 请注意，JSON_TABLE 和 XMLTABLE 有它们自己的原生 FOR ORDINALITY 语法，因此 WITH ORDINALITY 在那里是多余的。
jOOQ 支持像 CockroachDB 一样的语法，在任何 org.jooq.Table 上
SELECT * FROM UNNEST(ARRAY['a', 'b']) WITH ORDINALITY
create.select()
.from(unnest(array("a", "b")).withOrdinality())
.fetch();
可以使用 ROW_NUMBER() 窗口函数进行模拟。 这种派生表的排序稳定性取决于优化器实现，并且可能会“意外”中断，因为在大多数 RDBMS 中，派生表排序不需要稳定。 因此，除非可以毫无歧义地分配序号（例如，通过原生支持或因为模拟完全在 jOOQ 客户端实现），否则最好不要依赖确定性序号，除了从1到N的所有数字都将被唯一分配之外。
方言支持
此示例使用 jOOQ
select().from(unnest(array("a", "b")).withOrdinality().as("t", "a", "b"))
翻译成以下特定方言的表达式
Aurora Postgres, CockroachDB, H2, HSQLDB, Postgres
SELECT t.a, t.b FROM UNNEST(ARRAY['a', 'b']) WITH ORDINALITY 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(ARRAY['a', 'b']) WITH ORDINALITY t
) t
Databricks
SELECT t.a, t.b
FROM (
SELECT
array_table.COLUMN_VALUE,
row_number() OVER (ORDER BY (
SELECT 1
)) ordinal
FROM EXPLODE(ARRAY('a', 'b')) array_table (COLUMN_VALUE)
) t (a, b)
DuckDB
SELECT t.a, t.b
FROM (
SELECT
array_table.COLUMN_VALUE,
row_number() OVER () ordinal
FROM UNNEST(ARRAY['a', 'b']) array_table (COLUMN_VALUE)
) t (a, b)
ASE, Access, Aurora MySQL, ClickHouse, DB2, Derby, Exasol, Firebird, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！