如何在H2数据库中使用包含列的数组来获得n 1行

Question

我是新的H2数据库。例如，我在数据库中有3个表

table t1:

condition_id tbody> <<tr>EN 142868 151508

ID
1

需要对表T1中的行进行分组，并对表CONDITION中的行进行聚合:

CREATE TABLE T1(ID INT, DESC VARCHAR, SOMETHING VARCHAR, CONDITION_ID INT ARRAY)
AS VALUES (1, 'EN 142868 151508', 'XYZ', ARRAY[4686, 4687]);
CREATE TABLE CONDITION(CONDITION_ID INT, DESC VARCHAR, COLUMN3 VARCHAR)
AS VALUES (4686, 'desc1', 'something1'), (4687, 'desc2', 'something2');
SELECT T1.ID, T1.DESC, T1.SOMETHING,
LISTAGG(CONDITION.DESC || ' ' || CONDITION.COLUMN3, ', ') JOINED_COLUMN
FROM T1 LEFT JOIN CONDITION ON ARRAY_CONTAINS(T1.CONDITION_ID, CONDITION.CONDITION_ID)
GROUP BY T1.ID, T1.DESC, T1.SOMETHING;
> ID | DESC             | SOMETHING | JOINED_COLUMN
> -- | ---------------- | --------- | ----------------------------------
> 1  | EN 142868 151508 | XYZ       | desc1 something1, desc2 something2

如果要排除T1中没有CONDITION中相应条目的行，则使用内部JOIN代替LEFT JOIN。

如果需要精确的表项顺序，也可以在LISTAGG聚合函数中添加WITHIN GROUP (ODRER BY some_columns)子句:

LISTAGG(CONDITION.DESC || ' ' || CONDITION.COLUMN3, ', ')
WITHIN GROUP (ORDER BY CONDITION.CONDITION_ID)
JOINED_COLUMN

或者，您可以使用子查询:

SELECT T1.ID, T1.DESC, T1.SOMETHING,
(
SELECT LISTAGG(CONDITION.DESC || ' ' || CONDITION.COLUMN3, ', ')
FROM CONDITION
WHERE ARRAY_CONTAINS(T1.CONDITION_ID, CONDITION.CONDITION_ID)
) JOINED_COLUMN
FROM T1;