如何按method和月份生成矩阵表？附数据表与预期结果

要实现将methods和activities表关联，生成按方法分组、横向展示各月份活动列表的矩阵表，可通过条件聚合+字符串拼接的方式实现，以下是主流数据库的具体方案：

MySQL 实现 #

使用GROUP_CONCAT拼接活动名称，结合CASE WHEN筛选对应月份的数据：

SELECT
    m.name AS Method,
    GROUP_CONCAT(CASE WHEN MONTH(a.start) = 1 THEN a.name END SEPARATOR ', ') AS January,
    GROUP_CONCAT(CASE WHEN MONTH(a.start) = 2 THEN a.name END SEPARATOR ', ') AS Febuary
FROM
    methods m
LEFT JOIN
    activities a ON m.id = a.method_id
GROUP BY
    m.id, m.name;

• LEFT JOIN保证所有方法都会被展示，即使该方法在某个月份没有活动
• CASE WHEN匹配对应月份的活动，GROUP_CONCAT将同方法同月份的活动名称用逗号拼接
• 没有对应活动的月份会返回NULL，对应预期表中的空值

PostgreSQL 实现 #

PostgreSQL使用STRING_AGG替代GROUP_CONCAT，用EXTRACT(MONTH FROM ...)提取月份：

SELECT
    m.name AS Method,
    STRING_AGG(CASE WHEN EXTRACT(MONTH FROM a.start) = 1 THEN a.name END, ', ') AS January,
    STRING_AGG(CASE WHEN EXTRACT(MONTH FROM a.start) = 2 THEN a.name END, ', ') AS Febuary
FROM
    methods m
LEFT JOIN
    activities a ON m.id = a.method_id
GROUP BY
    m.id, m.name;

扩展说明 #

如果需要支持更多月份，只需继续添加对应月份的CASE WHEN分支即可；若月份是动态不固定的，可结合数据库的动态SQL功能生成对应列。

内容的提问来源于stack exchange，提问作者uu franki