创建SQL视图时如何添加由关联表指定列记录构成的数组列

核心思路是通过关联查询配合聚合函数，将关联表中匹配的多行ID合并为数组输出，以下是不同数据库的具体实现：

前提表结构说明 #

• 用户表：users，唯一标识列 username
• 帖子表：posts，外键关联列 username，帖子唯一标识列 post_id

PostgreSQL 实现（原生支持integer[]数组类型） #

PostgreSQL内置ARRAY_AGG聚合函数可直接生成整数数组，配合LEFT JOIN可保留无帖子的用户：

CREATE OR REPLACE VIEW user_posts_view AS
SELECT
  u.username,
  -- 过滤空值，无帖子的用户返回空数组而非NULL
  ARRAY_AGG(p.post_id) FILTER (WHERE p.post_id IS NOT NULL) AS posts
FROM users u
LEFT JOIN posts p ON u.username = p.username
GROUP BY u.username;

如果不需要展示无帖子的用户，将LEFT JOIN改为INNER JOIN即可，同时可省略FILTER子句。

MySQL 实现（5.7及以上版本） #

MySQL无原生整数数组类型，通常用JSON数组替代，使用JSON_ARRAYAGG函数实现：

CREATE OR REPLACE VIEW user_posts_view AS
SELECT
  u.username,
  JSON_ARRAYAGG(p.post_id) AS posts
FROM users u
LEFT JOIN posts p ON u.username = p.username
GROUP BY u.username;

效果示例 #

假设有用户alice对应post_id为2、4、6，用户bob无发布帖子，查询视图返回结果如下：

内容的提问来源于stack exchange，提问作者Kellen Cataldo