mysql - 在内部查询中使用 array_agg 和数组字符串

Question

我有以下代码:

DROP SCHEMA IF EXISTS s CASCADE;  
CREATE SCHEMA s;

CREATE TABLE "s"."t1"
(
    "c1" BigSerial PRIMARY KEY,
    "c2" BigInt NOT NULL,
    "c3" BigInt
)
WITH (OIDS=FALSE);

INSERT INTO s.t1 (c2) VALUES (10);
INSERT INTO s.t1 (c2, c3) VALUES (20, 10);
INSERT INTO s.t1 (c2, c3) VALUES (30, 10);

/* 1. */ SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2;

/* 2. */ SELECT t1.c1, ARRAY_TO_STRING(ARRAY_AGG((t2.c1)), ',') FROM s.t1 LEFT JOIN  s.t1 as t2
ON t2.c3 = t1.c2 GROUP BY t1.c1;

/* 3. */ SELECT c1, c2,
ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2)), ',') 
FROM s.t1 t1
GROUP BY c1;
DROP SCHEMA s CASCADE; 

1 个查询的输出:

 c1 
 ----
 2
 3
(2 rows)

2 查询:

 c1 | array_to_string 
 ----+-----------------
   1 | 2,3
   2 | 
   3 | 
  (3 rows)

3 查询给我一个错误:

   psql:/tmp/aggregate.sql:24: ERROR:  more than one row returned by a subquery used as an expression

3 查询使用 1 查询作为内部查询。有没有办法让查询 3 与内部查询一起作为 1 而不是恢复为 2。

3 输出应与 2 相同。

我知道错误消息说查询 1 当用作 3 的子查询时不能返回多于一行。

请原谅我对数据库的了解有限。

回答:

 SELECT c1, c2,
 ARRAY_TO_STRING((SELECT ARRAY_AGG(t2.c1) FROM s.t1 as t2 WHERE t2.c3 = t1.c2), ',') 
 FROM s.t1 t1
 GROUP BY c1;     

Answer 1

array_agg 函数是一个聚合函数，需要用在列上，而不是集合上。

试试这个:

/* 3. */ SELECT c1, c2,
ARRAY_TO_STRING((SELECT ARRAY_AGG(t3.c1) FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2), ',')
FROM s.t1 t1
GROUP BY c1;

或者使用string_agg函数:

/* 3. */ SELECT c1, c2,
(SELECT STRING_AGG(t3.c1::text, ',') FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2)
FROM s.t1 t1
GROUP BY c1;