SQL 第51题：查询成绩并在缺考时显示未考

题目

以学生左连离散数学成绩，查询学号与课程号；未选修该课的学生课程号显示为未考。每人一行。

知识点：LEFT JOIN、COALESCE

运行环境：本站提供 SQLite 环境，下面脚本可直接运行。若使用 MySQL，见文中对照；有差异的题目会同时给出 MySQL 写法。

思路

以学生为主，只去对离散数学（C02）这一门。没选这门课的人，连接结果里课程号是空的。

COALESCE(sc.cid, '未考') 把空显示成未考。每人一行，不是学生人数乘以课程数。

MySQL 与 SQLite 对照

SQLite 解法

-- 第51题：查询成绩并在缺考时显示未考
-- 样例数据：学生选课 + 员工部门。最后一条 SELECT 为本题查询。

DROP TABLE IF EXISTS score;
DROP TABLE IF EXISTS course;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS teacher;
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

CREATE TABLE teacher (
  tid TEXT PRIMARY KEY,
  tname TEXT NOT NULL
);

CREATE TABLE student (
  sid TEXT PRIMARY KEY,
  sname TEXT NOT NULL,
  ssex TEXT,
  birthday TEXT,
  city TEXT,
  email TEXT
);

CREATE TABLE course (
  cid TEXT PRIMARY KEY,
  cname TEXT NOT NULL,
  tid TEXT,
  credit INTEGER
);

CREATE TABLE score (
  sid TEXT,
  cid TEXT,
  score REAL,
  exam_on TEXT
);

CREATE TABLE dept (
  did TEXT PRIMARY KEY,
  dname TEXT NOT NULL
);

CREATE TABLE emp (
  eid TEXT PRIMARY KEY,
  ename TEXT NOT NULL,
  did TEXT,
  manager_id TEXT,
  salary INTEGER,
  hired_on TEXT
);

INSERT INTO teacher(tid, tname) VALUES
('T01', '何启明'),
('T02', '陆知秋'),
('T03', '裴南'),
('T04', '江衡');

INSERT INTO student(sid, sname, ssex, birthday, city, email) VALUES
('S01', '林晓', '女', '2003-03-12', '杭州', 'linxiao@campus.test'),
('S02', '周衡', '男', '2002-11-08', '宁波', 'zhouheng@campus.test'),
('S03', '沈予', '女', '2003-06-21', '杭州', 'shenyu@campus.test'),
('S04', '韩牧', '男', '2002-07-09', '温州', 'hanmu@campus.test'),
('S05', '叶安', '女', '2004-01-30', '杭州', 'yean@campus.test'),
('S06', '方迟', '男', '2001-12-19', '嘉兴', 'fangchi@campus.test'),
('S07', '苏晚', '女', '2003-09-04', '宁波', 'suwan@campus.test'),
('S08', '程远', '男', '2004-05-17', '杭州', 'chengyuan@campus.test'),
('S09', '顾星', '女', '2002-10-28', '绍兴', 'guxing@campus.test'),
('S10', '梁野', '男', '2001-02-08', '温州', 'liangye@campus.test'),
('S11', '夏禾', '女', '2003-08-16', '杭州', 'xiahe@campus.test'),
('S12', '许川', '男', '2002-04-25', '宁波', 'xuchuan@campus.test'),
('S13', '丁南', '女', '2004-04-18', '杭州', 'dingnan@campus.test'),
('S14', '蔡河', '男', '2001-06-03', '嘉兴', 'caihe@campus.test'),
('S15', '闻青', '女', '2002-08-22', '绍兴', 'wenqing@campus.test'),
('S16', '邱石', '男', '2003-12-01', '宁波', 'qiushi@campus.test');

INSERT INTO course(cid, cname, tid, credit) VALUES
('C01', '数据库原理', 'T02', 4),
('C02', '离散数学', 'T01', 3),
('C03', '操作系统', 'T03', 4),
('C04', '计算机网络', 'T02', 3),
('C05', '编译原理', 'T01', 4),
('C06', '线性代数', 'T03', 3),
('C07', '软件工程', 'T04', 2),
('C08', '人工智能', 'T04', 3),
('C09', '毕业实习', 'T03', 1);

INSERT INTO score(sid, cid, score, exam_on) VALUES
('S01', 'C01', 88, '2025-01-08'),
('S01', 'C02', 76, '2025-01-10'),
('S01', 'C03', 91, '2025-01-12'),
('S01', 'C04', 85, '2025-01-09'),
('S01', 'C05', 72, '2025-01-11'),
('S01', 'C06', 90, '2025-01-13'),
('S01', 'C07', 68, '2025-01-08'),
('S01', 'C08', 80, '2025-02-15'),
('S02', 'C01', 70, '2025-01-08'),
('S02', 'C02', 82, '2025-01-10'),
('S02', 'C03', 64, '2025-01-12'),
('S02', 'C04', 55, '2025-01-09'),
('S03', 'C01', 90, '2025-01-08'),
('S03', 'C02', 90, '2025-01-10'),
('S03', 'C03', 85, '2025-01-12'),
('S03', 'C05', 78, '2025-01-11'),
('S04', 'C01', 45, '2025-01-08'),
('S04', 'C02', 52, '2025-01-10'),
('S04', 'C03', 40, '2025-01-12'),
('S04', 'C04', 0, '2025-01-09'),
('S05', 'C01', 79, '2025-01-08'),
('S05', 'C02', 88, '2025-01-10'),
('S05', 'C03', 70, '2025-01-09'),
('S06', 'C01', 0, '2025-01-08'),
('S06', 'C03', 58, '2025-01-12'),
('S06', 'C06', 41, '2025-01-13'),
('S07', 'C02', 94, '2025-01-10'),
('S07', 'C03', 90, '2025-01-12'),
('S07', 'C07', 88, '2025-01-11'),
('S08', 'C01', 86, '2025-01-08'),
('S08', 'C02', 61, '2025-01-10'),
('S08', 'C03', 73, '2025-01-12'),
('S08', 'C08', 69, '2025-02-15'),
('S09', 'C01', 92, '2025-01-08'),
('S09', 'C03', 81, '2025-01-12'),
('S09', 'C04', 77, '2025-01-09'),
('S10', 'C02', 55, '2025-01-10'),
('S10', 'C03', 47, '2025-01-12'),
('S11', 'C01', 77, '2025-01-08'),
('S11', 'C02', 80, '2025-01-10'),
('S11', 'C03', 79, '2025-01-12'),
('S11', 'C05', 66, '2025-01-11'),
('S12', 'C01', 60, '2025-01-08'),
('S13', 'C02', 84, '2025-01-10'),
('S13', 'C07', 91, '2025-01-11'),
('S14', 'C01', 58, '2025-01-08'),
('S14', 'C06', 73, '2025-01-13'),
('S15', 'C03', 62, '2025-01-12'),
('S15', 'C08', 0, '2025-02-15');

INSERT INTO dept(did, dname) VALUES
('D01', '研发部'),
('D02', '教学部'),
('D03', '市场部');

INSERT INTO emp(eid, ename, did, manager_id, salary, hired_on) VALUES
('E01', '陈省', 'D01', NULL, 18000, '2020-03-01'),
('E02', '吴舟', 'D01', 'E01', 12000, '2022-05-12'),
('E03', '唐宁', 'D01', 'E01', 19000, '2021-08-20'),
('E04', '沈泊', 'D02', 'E01', 15000, '2021-11-04'),
('E05', '齐末', 'D02', 'E04', 9000, '2024-04-01'),
('E06', '米白', 'D03', 'E01', 11000, '2023-02-18'),
('E07', '蓝秋', 'D03', 'E06', 8000, '2024-09-01'),
('E08', '贺川', 'D03', 'E06', 21000, '2022-12-10');

-- 本题查询
SELECT st.sid, COALESCE(sc.cid, '未考') AS cid
FROM student st
LEFT JOIN score sc ON sc.sid = st.sid AND sc.cid = 'C02'
ORDER BY st.sid;

MySQL 解法

查询部分与 SQLite 的差异如下（建表请改用 VARCHAR / DECIMAL / DATE）。

SELECT st.sid, IFNULL(sc.cid, '未考') AS cid
FROM student st
LEFT JOIN score sc ON sc.sid = st.sid AND sc.cid = 'C02'
ORDER BY st.sid;

运行示例

sid | cid
----+----
S01 | C02
S02 | C02
S03 | C02
S04 | C02
S05 | C02
S06 | 未考 
S07 | C02
S08 | C02
S09 | 未考 
S10 | C02
S11 | C02
S12 | 未考 
S13 | C02
S14 | 未考 
S15 | 未考 
S16 | 未考