PostgreSQL多关系表查询实现单选手数据合并为一行方案咨询

一、表结构优化建议 #

• 当前styles_preferred、styles_optional表用多列存储风格ID的设计不符合数据库设计范式，可扩展性差，建议调整为单条记录存单个关联关系的结构：

-- 优化后的选手风格关联表，替代原来的两张关联表
CREATE TABLE fighter_styles (
    fighter_id INT REFERENCES fighters(fighter_id) ON DELETE CASCADE,
    style_id INT REFERENCES styles(style_id) ON DELETE CASCADE,
    is_preferred BOOLEAN NOT NULL, -- 标记是首选还是可选
    PRIMARY KEY(fighter_id, style_id)
);

• 调整后不需要预留固定列数，后续新增风格不需要改表结构，查询也更简便。

二、现有表结构下的查询实现 #

不需要改原有表结构的前提下，用PostgreSQL的string_agg聚合函数实现按选手合并结果，查询语句如下：

SELECT 
    CONCAT('(', f.fighter_name, ', ', string_agg(DISTINCT CONCAT(s.fight_style_name, s.fight_level), ', ' ORDER BY CONCAT(s.fight_style_name, s.fight_level)), ')') AS row
FROM fighters f
LEFT JOIN styles_preferred sp ON sp.fighter_id = f.fighter_id
LEFT JOIN styles_optional so ON so.fighter_id = f.fighter_id
LEFT JOIN styles s 
    ON s.style_id IN (sp.style_id1, sp.style_id2, sp.style_id3, sp.style_id4, so.style_id1, so.style_id2, so.style_id3, so.style_id4)
WHERE 
    -- 保留原过滤条件
    sp.style_id2 = 2002 
    OR sp.style_id4 = 2004 
    OR so.style_id4 = 2004
GROUP BY f.fighter_id, f.fighter_name;

查询结果验证 #

执行后会返回期望的格式：

三、适配CSV导出的扩展建议 #

如果要导出CSV做排序，不需要拼接成括号包裹的单行，可以直接输出分列结果，后续处理更方便：

SELECT 
    f.fighter_name,
    string_agg(DISTINCT CONCAT(s.fight_style_name, s.fight_level), ', ') AS all_fight_styles
FROM fighters f
LEFT JOIN styles_preferred sp ON sp.fighter_id = f.fighter_id
LEFT JOIN styles_optional so ON so.fighter_id = f.fighter_id
LEFT JOIN styles s 
    ON s.style_id IN (sp.style_id1, sp.style_id2, sp.style_id3, sp.style_id4, so.style_id1, so.style_id2, so.style_id3, so.style_id4)
WHERE 
    sp.style_id2 = 2002 
    OR sp.style_id4 = 2004 
    OR so.style_id4 = 2004
GROUP BY f.fighter_id, f.fighter_name;

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