SQL技术求助：如何生成含空成绩的学生科目成绩视图？

核心思路 #

要实现每个学生对应所有科目（即使未参加考试），关键是先生成所有学生与科目的全量组合，再通过左连接关联原表获取成绩，未匹配到的成绩自动设为null。

假设原表结构 #

假设你的test表字段为：student_id（学生ID）、student_name（学生姓名）、subject（科目）、score（成绩）。如果字段名不同，只需替换对应名称即可。

实现SQL语句 #

CREATE VIEW student_subject_scores AS
SELECT 
    s.student_id,
    s.student_name,
    sub.subject,
    t.score
FROM 
    -- 提取所有不重复的学生信息
    (SELECT DISTINCT student_id, student_name FROM test) s
-- 交叉连接生成学生与科目的全量组合
CROSS JOIN 
    -- 提取所有不重复的科目
    (SELECT DISTINCT subject FROM test) sub
-- 左连接原表，匹配对应学生和科目的成绩
LEFT JOIN 
    test t ON s.student_id = t.student_id AND sub.subject = t.subject
-- 可选：按学生ID和科目排序，方便查看
ORDER BY 
    s.student_id, sub.subject;

为什么之前的LEFT JOIN没生效？ #

如果直接用原表做LEFT JOIN，无法自动生成“学生未参加的科目”记录——因为原表本身不存在这些组合。必须先通过CROSS JOIN（交叉连接）生成所有可能的学生-科目配对，再左连原表才能补全缺失的记录。

示例效果 #

比如原test表数据：

生成的视图数据会是：

适配不同数据库 #

上述语句兼容MySQL、PostgreSQL、SQL Server等主流数据库，若你的数据库有特殊语法（比如Oracle的CREATE VIEW需要权限），只需微调即可。

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