MULTISET_AGG
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
合成的 MULTISET_AGG() 聚合函数将组内容收集到嵌套集合中，就像 MULTISET 值构造函数（了解 其他合成 SQL 语法）。
SELECT
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
MULTISET_AGG(
BOOK.ID,
BOOK.TITLE,
LANGUAGE.CD
)
FROM AUTHOR
JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
JOIN LANGUAGE ON BOOK.LANGUAGE_ID = LANGUAGE.ID
GROUP BY
AUTHOR.ID,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME
ORDER BY AUTHOR.ID
create.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
multisetAgg(
BOOK.ID,
BOOK.TITLE,
BOOK.language().CD
).as("books"))
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
.groupBy(
AUTHOR.ID,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME)
.orderBy(AUTHOR.ID)
.fetch()
结果是
+----------+---------+---------------------------------------+ |first_name|last_name|books | +----------+---------+---------------------------------------+ |George |Orwell |[(2, Animal Farm, en), (1, 1984, en)] | |Paulo |Coelho |[(4, Brida, de), (3, O Alquimista, pt)]| +----------+---------+---------------------------------------+
multisetAgg(BOOK.ID, BOOK.TITLE)
翻译成以下特定方言的表达式
Aurora Postgres, CockroachDB, Postgres, YugabyteDB
jsonb_agg(jsonb_build_array(BOOK.ID, BOOK.TITLE))
BigQuery
array_agg(`nested__ID`, `nested__TITLE`)
DB2
xmlelement(
NAME result,
xmlagg(xmlelement(
NAME record,
xmlelement(NAME v0, BOOK.ID),
xmlelement(
NAME v1,
xmlattributes(
CASE
WHEN BOOK.TITLE IS NULL THEN 'true'
END AS "xsi:nil"
),
BOOK.TITLE
)
))
)
DuckDB
array_agg(ROW (BOOK.ID, BOOK.TITLE))
H2
json_arrayagg(json_array(BOOK.ID, BOOK.TITLE NULL ON NULL))
MariaDB, MySQL
json_merge_preserve(
'[]',
concat(
'[',
group_concat(json_array(BOOK.ID, BOOK.TITLE) SEPARATOR ','),
']'
)
)
Oracle
json_arrayagg(json_array(BOOK.ID, BOOK.TITLE NULL ON NULL RETURNING clob) FORMAT JSON RETURNING clob)
Snowflake
array_agg(array_construct(coalesce(
to_variant(BOOK.ID),
parse_json('null')
), coalesce(
to_variant(BOOK.TITLE),
parse_json('null')
)))
SQLite
json_group_array(json_array(BOOK.ID, BOOK.TITLE))
Teradata
xmlelement(
NAME "result",
xmlagg(xmlelement(
NAME record,
xmlelement(NAME v0, BOOK.ID),
xmlelement(
NAME v1,
xmlattributes(
CASE
WHEN BOOK.TITLE IS NULL THEN 'true'
END AS nil
),
BOOK.TITLE
)
))
)
Trino
cast(array_agg(CAST(ARRAY[ CAST(BOOK.ID AS json), CAST(BOOK.TITLE AS json) ] AS json)) AS json)
ASE, Access, Aurora MySQL, ClickHouse, Databricks, Derby, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLServer, Sybase, Vertica
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！