如何将score_table所有行插入viewing_table？（pgAdmin4/PostgreSQL）

当然可以实现，你当前的SQL存在几个问题：

• 子查询里的student_number = student_number因内外层字段名重复，导致条件永远为真，返回的是所有符合item和item_id的score，而非当前学生的分数，会引发单行子查询返回多行的错误
• 用DISTINCT虽能避免重复插入同一学生的数据，但效率不如直接分组
• 最后一个子查询后多了个逗号，会导致语法错误

针对PostgreSQL，推荐用条件聚合的方式实现行列转换，这是处理这类需求的标准方案：

INSERT INTO viewing_table (student_number, Running1, Running2, Running3, Swimming1, Swimming2, Swimming3)
SELECT 
    student_number,
    MAX(score) FILTER (WHERE item = 'Running' AND item_id = '1') AS Running1,
    MAX(score) FILTER (WHERE item = 'Running' AND item_id = '2') AS Running2,
    MAX(score) FILTER (WHERE item = 'Running' AND item_id = '3') AS Running3,
    MAX(score) FILTER (WHERE item = 'Swimming' AND item_id = '1') AS Swimming1,
    MAX(score) FILTER (WHERE item = 'Swimming' AND item_id = '2') AS Swimming2,
    MAX(score) FILTER (WHERE item = 'Swimming' AND item_id = '3') AS Swimming3
FROM score_table
GROUP BY student_number;

逻辑说明： #

• 按student_number分组，确保每个学生只生成一行数据
• 用FILTER子句筛选对应项目和编号的分数，MAX聚合函数用来取出该学生对应项目的唯一分数（如果每个学生每个item+item_id组合只有一条数据，用MAX/AVG/SUM都可以）
• 转换后的结构正好匹配viewing_table的列结构，可直接插入

如果你的PostgreSQL版本低于9.4（不支持FILTER），可以用CASE WHEN替代：

INSERT INTO viewing_table (student_number, Running1, Running2, Running3, Swimming1, Swimming2, Swimming3)
SELECT 
    student_number,
    MAX(CASE WHEN item = 'Running' AND item_id = '1' THEN score END) AS Running1,
    MAX(CASE WHEN item = 'Running' AND item_id = '2' THEN score END) AS Running2,
    MAX(CASE WHEN item = 'Running' AND item_id = '3' THEN score END) AS Running3,
    MAX(CASE WHEN item = 'Swimming' AND item_id = '1' THEN score END) AS Swimming1,
    MAX(CASE WHEN item = 'Swimming' AND item_id = '2' THEN score END) AS Swimming2,
    MAX(CASE WHEN item = 'Swimming' AND item_id = '3' THEN score END) AS Swimming3
FROM score_table
GROUP BY student_number;

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