非全列覆盖场景下SQL多行转单行多列的实现方案咨询

推荐使用兼容性更强的条件聚合方案实现行转列，该方案同时适配你当前使用的LibreOffice Base环境，以及后续计划迁移的MySQL环境，无需修改核心逻辑即可运行：

SELECT 
    "subject" AS "subject（学科）",
    SUM(CASE WHEN "judgement" = '优秀' THEN "total" ELSE 0 END) AS "优秀",
    SUM(CASE WHEN "judgement" = '良好' THEN "total" ELSE 0 END) AS "良好",
    SUM(CASE WHEN "judgement" = '待改进' THEN "total" ELSE 0 END) AS "待改进"
FROM (
    SELECT "subject", "judgement", SUM("teaching") AS total FROM (
        (SELECT "opt1" AS "subject", "teach_opt1" AS "judgement", COUNT(*) AS "teaching" FROM "raw" WHERE "opt1" NOT LIKE 'None' AND "opt1" NOT LIKE '' AND "teach_opt1" NOT LIKE 'Don%do it' GROUP BY "opt1", "teach_opt1")
        UNION ALL
        (SELECT "opt2" AS "subject", "teach_opt2" AS "judgement", COUNT(*) AS "teaching" FROM "raw" WHERE "opt2" NOT LIKE 'None' AND "opt2" NOT LIKE '' AND "teach_opt2" NOT LIKE 'Don%do it' GROUP BY "opt2", "teach_opt2")
        UNION ALL
        (SELECT "opt3" AS "subject", "teach_opt3" AS "judgement", COUNT(*) AS "teaching" FROM "raw" WHERE "opt3" NOT LIKE 'None' AND "opt3" NOT LIKE '' AND "teach_opt3" NOT LIKE 'Don%do it' GROUP BY "opt3", "teach_opt3")
        UNION ALL
        (SELECT "opt4" AS "subject", "teach_opt4" AS "judgement", COUNT(*) AS "teaching" FROM "raw" WHERE "opt4" NOT LIKE 'None' AND "opt4" NOT LIKE '' AND "teach_opt4" NOT LIKE 'Don%do it' GROUP BY "opt4", "teach_opt4")
    ) t
    GROUP BY "subject", "judgement"
) final
GROUP BY "subject"
ORDER BY "subject";

逻辑说明 #

• 内层保留你原有已经调试通过的学科-评价统计逻辑，输出每个学科对应每个评价的总数量
• 外层通过CASE WHEN对评价类型做判断，匹配到对应评价时取统计值，否则取0，再通过SUM聚合得到每个学科对应不同评价的总数值，缺少对应评价的学科会自动返回0
• 该方案无需依赖数据库特定的PIVOT语法，且MySQL本身没有原生PIVOT关键字，条件聚合是目前兼容性最好、维护成本最低的实现方式。

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