postgresql - 获取每一行的列表值(来自另一个表的 ID 列表)

Question

我有一个非常普通的表，它有一个指向另一个表的外键；例如:

CREATE TABLE table_a (
  id serial NOT NULL,
  name text,
  CONSTRAINT table_a_pkey PRIMARY KEY (id)
);
CREATE TABLE table_b (
  id serial NOT NULL,
  a_id integer,       -- The foreign key
  CONSTRAINT table_b_pkey PRIMARY KEY (id),
  CONSTRAINT table_b_a_id_fkey FOREIGN KEY (a_id)
      REFERENCES table_a (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
INSERT INTO table_a
     VALUES (1, 'First row');
-- 2 entries in table_b which refer to the existing table_a row:
INSERT INTO table_b
     VALUES (11, 1), (12, 1);

现在我想要一个 View ，它给我一个列表，其中包含引用当前 table_a 行的 table_b 行的所有 id:

SELECT a.name,
       (SELECT b.id
          FROM table_b b
         WHERE b.id = a.id) AS b_ids
  FROM table_a a;

但是，b_ids 列是空的；我想要某种列表，其中包含值 11 和 12。

我在某处读到子选择只能产生一列(在这种情况下对我来说没问题)并且只能产生一行(这可以解释上述查询对我不起作用)。如果这是真的 - 如何才能做到这一点？或者我真的需要为程序中的每个 table_a 行发出 SELECT 请求吗？

我希望它能与 PostgreSQL 9.1 和 9.3 一起使用。

Answer 1

您可以使用array_agg 函数:

SELECT table_a.name, array_agg(table_b.id)
FROM table_a
LEFT OUTER JOIN table_b
ON table_a.id = table_b.a_id
GROUP BY table_a.name;

┌───────────┬───────────┐
│   name    │ array_agg │
├───────────┼───────────┤
│ First row │ {11,12}   │
└───────────┴───────────┘
(1 row)