题目

查询每条成绩的考试日与该学生生日相差的天数。

知识点：日期间隔、JOIN

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

思路

每条成绩看考试日距离该学生生日过了多少天。成绩连学生，用两个日期相减。

SQLite：儒略日相减再取整。MySQL：DATEDIFF(exam_on, birthday)。

MySQL 与 SQLite 对照

场景MySQLSQLite
日期间隔天数DATEDIFF(sold_on, joined_on)CAST(JULIANDAY(sold_on) - JULIANDAY(joined_on) AS INT)
年龄（整岁）TIMESTAMPDIFF(YEAR, birthday, '2025-03-12')CAST(STRFTIME('%Y', '2025-03-12') AS INT) - CAST(STRFTIME('%Y', birthday) AS INT) - CASE WHEN STRFTIME('%m-%d', '2025-03-12') < STRFTIME('%m-%d', birthday) THEN 1 ELSE 0 END

SQLite 解法

-- 第68题：查询考试日距离出生日期的天数
-- 样例数据：学生选课 + 员工部门。最后一条 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 sc.sid, sc.cid, sc.exam_on, st.birthday,
  CAST(JULIANDAY(sc.exam_on) - JULIANDAY(st.birthday) AS INT) AS days_alive
FROM score sc JOIN student st ON st.sid = sc.sid
ORDER BY sc.sid, sc.cid;

MySQL 解法

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

SELECT sc.sid, sc.cid, sc.exam_on, st.birthday, DATEDIFF(sc.exam_on, st.birthday) AS days_alive
FROM score sc JOIN student st ON st.sid = sc.sid
ORDER BY sc.sid, sc.cid;

运行示例

sid | cid | exam_on    | birthday   | days_alive
----+-----+------------+------------+-----------
S01 | C01 | 2025-01-08 | 2003-03-12 | 7973      
S01 | C02 | 2025-01-10 | 2003-03-12 | 7975      
S01 | C03 | 2025-01-12 | 2003-03-12 | 7977      
S01 | C04 | 2025-01-09 | 2003-03-12 | 7974      
S01 | C05 | 2025-01-11 | 2003-03-12 | 7976      
S01 | C06 | 2025-01-13 | 2003-03-12 | 7978      
S01 | C07 | 2025-01-08 | 2003-03-12 | 7973      
S01 | C08 | 2025-02-15 | 2003-03-12 | 8011      
S02 | C01 | 2025-01-08 | 2002-11-08 | 8097      
S02 | C02 | 2025-01-10 | 2002-11-08 | 8099      
S02 | C03 | 2025-01-12 | 2002-11-08 | 8101      
S02 | C04 | 2025-01-09 | 2002-11-08 | 8098      
S03 | C01 | 2025-01-08 | 2003-06-21 | 7872      
S03 | C02 | 2025-01-10 | 2003-06-21 | 7874      
S03 | C03 | 2025-01-12 | 2003-06-21 | 7876      
S03 | C05 | 2025-01-11 | 2003-06-21 | 7875      
S04 | C01 | 2025-01-08 | 2002-07-09 | 8219      
S04 | C02 | 2025-01-10 | 2002-07-09 | 8221      
S04 | C03 | 2025-01-12 | 2002-07-09 | 8223      
S04 | C04 | 2025-01-09 | 2002-07-09 | 8220      
S05 | C01 | 2025-01-08 | 2004-01-30 | 7649      
S05 | C02 | 2025-01-10 | 2004-01-30 | 7651      
S05 | C03 | 2025-01-09 | 2004-01-30 | 7650      
S06 | C01 | 2025-01-08 | 2001-12-19 | 8421      
S06 | C03 | 2025-01-12 | 2001-12-19 | 8425      
S06 | C06 | 2025-01-13 | 2001-12-19 | 8426      
S07 | C02 | 2025-01-10 | 2003-09-04 | 7799      
S07 | C03 | 2025-01-12 | 2003-09-04 | 7801      
S07 | C07 | 2025-01-11 | 2003-09-04 | 7800      
S08 | C01 | 2025-01-08 | 2004-05-17 | 7541      
S08 | C02 | 2025-01-10 | 2004-05-17 | 7543      
S08 | C03 | 2025-01-12 | 2004-05-17 | 7545      
S08 | C08 | 2025-02-15 | 2004-05-17 | 7579      
S09 | C01 | 2025-01-08 | 2002-10-28 | 8108      
S09 | C03 | 2025-01-12 | 2002-10-28 | 8112      
S09 | C04 | 2025-01-09 | 2002-10-28 | 8109      
S10 | C02 | 2025-01-10 | 2001-02-08 | 8737      
S10 | C03 | 2025-01-12 | 2001-02-08 | 8739      
S11 | C01 | 2025-01-08 | 2003-08-16 | 7816      
S11 | C02 | 2025-01-10 | 2003-08-16 | 7818      
S11 | C03 | 2025-01-12 | 2003-08-16 | 7820      
S11 | C05 | 2025-01-11 | 2003-08-16 | 7819      
S12 | C01 | 2025-01-08 | 2002-04-25 | 8294      
S13 | C02 | 2025-01-10 | 2004-04-18 | 7572      
S13 | C07 | 2025-01-11 | 2004-04-18 | 7573      
S14 | C01 | 2025-01-08 | 2001-06-03 | 8620      
S14 | C06 | 2025-01-13 | 2001-06-03 | 8625      
S15 | C03 | 2025-01-12 | 2002-08-22 | 8179      
S15 | C08 | 2025-02-15 | 2002-08-22 | 8213