如何使用H2数据库中的列Containg数组以获取N 1行

Question

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

：

desc	somending_id	1	1
en	142868 142868 151508	xyz	4686，4687]

表条件条件条件

desc	column3	4686
desc1	dess	4687
desc2	desc2 desc2	desc2 something

2

表	[	condition_id	sometsing
ID	T1 142868 151508	xyz	desc1 sosings1，desc2 somethin2

如何做到这一点，我尝试了array_contains，但是它给出了2行，结果是't1ID t1desc t1something Join join join ofin ofin coin of Cinter 1 en 142868 151508 151508 xyz desc1 xyc1 somets 1 1 en 142868 142868 151508 xyz xyz22222

。先感谢您

Answer 1

您需要从表t1将行分组，然后从表条件汇总行：

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将行排除，而无需在条件，使用内部JOIN而不是左JOIN。

您可能还需要在组中添加（odrer by Some_columns）从listAgg汇总函数如果需要一些确切的条目：

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

或者，您可以使用subquery：

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;