题目
按学生分区、课程号排序，查询上一门课分数。
知识点：LAG
运行环境：本站提供在线 SQL 运行环境。
思路
上一门按课程号顺序，不是按考试日。同一学生分区，ORDER BY cid，LAG(score, 1)。
每个学生编号最小的那门没有上一门，prev_score 为空。
解法
-- 第77题：查询同一学生上一门课的分数
-- 样例数据：学生选课 + 员工部门。最后一条 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 sid, cid, score, LAG(score, 1) OVER (PARTITION BY sid ORDER BY cid) AS prev_score FROM score ORDER BY sid, cid;
运行示例
sid | cid | score | prev_score
----+-----+-------+-----------
S01 | C01 | 88.0 | NULL
S01 | C02 | 76.0 | 88.0
S01 | C03 | 91.0 | 76.0
S01 | C04 | 85.0 | 91.0
S01 | C05 | 72.0 | 85.0
S01 | C06 | 90.0 | 72.0
S01 | C07 | 68.0 | 90.0
S01 | C08 | 80.0 | 68.0
S02 | C01 | 70.0 | NULL
S02 | C02 | 82.0 | 70.0
S02 | C03 | 64.0 | 82.0
S02 | C04 | 55.0 | 64.0
S03 | C01 | 90.0 | NULL
S03 | C02 | 90.0 | 90.0
S03 | C03 | 85.0 | 90.0
S03 | C05 | 78.0 | 85.0
S04 | C01 | 45.0 | NULL
S04 | C02 | 52.0 | 45.0
S04 | C03 | 40.0 | 52.0
S04 | C04 | 0.0 | 40.0
S05 | C01 | 79.0 | NULL
S05 | C02 | 88.0 | 79.0
S05 | C03 | 70.0 | 88.0
S06 | C01 | 0.0 | NULL
S06 | C03 | 58.0 | 0.0
S06 | C06 | 41.0 | 58.0
S07 | C02 | 94.0 | NULL
S07 | C03 | 90.0 | 94.0
S07 | C07 | 88.0 | 90.0
S08 | C01 | 86.0 | NULL
S08 | C02 | 61.0 | 86.0
S08 | C03 | 73.0 | 61.0
S08 | C08 | 69.0 | 73.0
S09 | C01 | 92.0 | NULL
S09 | C03 | 81.0 | 92.0
S09 | C04 | 77.0 | 81.0
S10 | C02 | 55.0 | NULL
S10 | C03 | 47.0 | 55.0
S11 | C01 | 77.0 | NULL
S11 | C02 | 80.0 | 77.0
S11 | C03 | 79.0 | 80.0
S11 | C05 | 66.0 | 79.0
S12 | C01 | 60.0 | NULL
S13 | C02 | 84.0 | NULL
S13 | C07 | 91.0 | 84.0
S14 | C01 | 58.0 | NULL
S14 | C06 | 73.0 | 58.0
S15 | C03 | 62.0 | NULL
S15 | C08 | 0.0 | 62.0