表名和字段
学生表
Student(s_id,s_name,s_birth,s_sex)：学生编号、学生姓名、出生年月、学生性别。
课程表
Course(c_id,c_name,t_id)：课程编号、课程名称、教师编号。
教师表
Teacher(t_id,t_name)：教师编号、教师姓名。
成绩表
Score(s_id,c_id,s_score)：学生编号、课程编号、分数。
建表语句
# 建表
# 学生表
CREATE TABLE `student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
# 课程表
CREATE TABLE `course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
# 教师表
CREATE TABLE `teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
# 成绩表
CREATE TABLE `score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
# 插入数据
# 插入学生表测试数据
insert into Student values('1', '赵雷', '1990-01-01', '男');
insert into Student values('2', '钱电', '1990-12-21', '男');
insert into Student values('3', '孙风', '1990-05-20', '男');
insert into Student values('4', '李云', '1990-08-06', '男');
insert into Student values('5', '周梅', '1991-12-01', '女');
insert into Student values('6', '吴兰', '1992-03-01', '女');
insert into Student values('7', '郑竹', '1989-07-01', '女');
insert into Student values('8', '王菊', '1990-01-20', '女');
# 插入课程表测试数据
insert into Course values('1', '语文', '2');
insert into Course values('2', '数学', '1');
insert into Course values('3', '英语', '3');
# 插入教师表测试数据
insert into Teacher values('1', '张三');
insert into Teacher values('2', '李四');
insert into Teacher values('3', '王五');
# 插入成绩表测试数据
insert into Score values('1', '1', 80);
insert into Score values('1', '2', 90);
insert into Score values('1', '3', 99);
insert into Score values('2', '1', 70);
insert into Score values('2', '2', 60);
insert into Score values('2', '3', 80);
insert into Score values('3', '1', 80);
insert into Score values('3', '2', 80);
insert into Score values('3', '3', 80);
insert into Score values('4', '1', 50);
insert into Score values('4', '2', 30);
insert into Score values('4', '3', 20);
insert into Score values('5', '1', 76);
insert into Score values('5', '2', 87);
insert into Score values('6', '1', 31);
insert into Score values('6', '3', 34);
insert into Score values('7', '2', 89);
insert into Score values('7', '3', 98);
题目及答案
# 1.查询"1"课程比"2"课程成绩高的学生的信息及课程分数。
SELECT
student.*,
s1.s_score AS "语文成绩",
s2.s_score AS "数学成绩"
FROM
student
LEFT JOIN
score AS s1
ON
student.s_id = s1.s_id AND s1.c_id = 1
LEFT JOIN
score AS s2
ON
student.s_id = s2.s_id AND s2.c_id = 2
WHERE
s1.s_score > s2.s_score;
# 2.查询"1"课程比"2"课程成绩低的学生的信息及课程分数。
SELECT
student.*,
s1.s_score AS "语文成绩",
s2.s_score AS "数学成绩"
FROM
student
LEFT JOIN
score AS s1
ON
s1.s_id = student.s_id AND s1.c_id = 1
LEFT JOIN
score AS s2
ON
s2.s_id = student.s_id AND s2.c_id = 2
WHERE
s1.s_score < s2.s_score;
# 3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。
SELECT
student.s_id AS "学生编号",
student.s_name AS "学生姓名",
ROUND(AVG(score.s_score),2) AS "平均成绩"
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
GROUP BY
student.s_id
HAVING
AVG(score.s_score) >= 60;
# 4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（包括有成绩的和无成绩的）。
SELECT
student.s_id AS "学生编号",
student.s_name AS "学生姓名",
IFNULL(ROUND(AVG(score.s_score),2),0) AS "平均成绩"
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
GROUP BY
student.s_id
HAVING
AVG(score.s_score) < 60 OR AVG(score.s_score) IS NULL;
# 5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。
SELECT
student.s_id AS "学生编号",
student.s_name AS "学生姓名",
COUNT(course.c_id) AS "选课总数",
IFNULL(SUM(score.s_score),0) AS "所有课程总成绩"
FROM
student
LEFT JOIN
score
ON
student.s_id = score.s_id
LEFT JOIN
course
ON
course.c_id = score.c_id
GROUP BY
student.s_id;
# 6.查询"李"姓老师的数量。
SELECT
COUNT(t_id)
FROM
teacher
WHERE
t_name LIKE "李%";
# 7.查询学过"张三"老师授课的同学的信息。
SELECT
student.*
FROM
student
LEFT JOIN
score
ON
student.s_id = score.s_id
LEFT JOIN
course
ON
score.c_id = course.c_id
LEFT JOIN
teacher
ON
course.t_id = teacher.t_id
WHERE
teacher.t_name = "张三";
# 8.查询没学过"张三"老师授课的同学的信息。
SELECT
student.*
FROM
student
WHERE
student.s_id NOT IN
(
SELECT
student.s_id
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
WHERE
score.c_id IN
(
SELECT
course.c_id
FROM
course
LEFT JOIN
teacher
ON
course.t_id = teacher.t_id
WHERE
teacher.t_name = "张三"
)
)
# 9.查询学过编号为"1"并且也学过编号为"2"的课程的同学的信息。
SELECT
student.*
FROM
student
INNER JOIN
score
ON
score.s_id = student.s_id
INNER JOIN
course
ON
course.c_id = score.c_id
GROUP BY
student.s_id
HAVING
SUM(IF(course.c_id = 1 OR course.c_id = 2, 1, 0)) > 1;
# 10.查询学过编号为"1"但是没有学过编号为"2"的课程的同学的信息。
SELECT
student.*
FROM
student
INNER JOIN
score
ON
score.s_id = student.s_id AND score.c_id = 1
WHERE
student.s_id NOT IN
(
SELECT
student.s_id
FROM
student
INNER JOIN
score
ON
score.s_id = student.s_id AND score.c_id = 2
);
# 11.查询没有学全所有课程的同学的信息。
SELECT
student.*
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
GROUP BY
student.s_id
HAVING
COUNT(c_id) < (
SELECT
COUNT(c_id)
FROM
course
);
# 12.查询至少有一门课与学号为"1的同学所学相同的同学的信息。
SELECT
student.*
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
WHERE
c_id IN (
SELECT
c_id
FROM
score
LEFT JOIN
student
ON
score.s_id = student.s_id
WHERE
student.s_id = 1
)
GROUP BY
student.s_id;
# 13.查询和"1"号的同学学习的课程完全相同的其他同学的信息。
SELECT
student.*
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
GROUP BY
student.s_id
HAVING
GROUP_CONCAT(c_id) = (
SELECT
GROUP_CONCAT(c_id)
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
GROUP BY
student.s_id
HAVING
student.s_id = 1
);
# 14.查询没学过"张三"老师讲授的任一门课程的学生姓名。
SELECT
s_name
FROM
student
WHERE
s_id NOT IN (
SELECT
s_id
FROM
score
INNER JOIN
course
ON
course.c_id = score.c_id
INNER JOIN
teacher
ON
teacher.t_id = course.t_id AND t_name = "张三"
);
# 15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。
SELECT
student.s_id AS "学号",
s_name AS "姓名",
IFNULL(ROUND(AVG(s_score),2),0) AS "平均成绩"
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
GROUP BY
student.s_id
HAVING
SUM(IF(s_score < 60, 1, 0)) >= 2;
SELECT st.s_id,st.s_name, sc.* FROM score AS sc LEFT JOIN student AS st ON sc.s_id = st.s_id WHERE sc.s_score < 60 GROUP BY sc.s_id;
# 16.检索"1"课程分数小于60，按分数降序排列的学生信息。
SELECT
student.*,
s_score AS "分数"
FROM
student
INNER JOIN
score
ON
score.s_id = student.s_id AND c_id = 1
WHERE
s_score < 60
ORDER BY
s_score DESC;
# 17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。
SELECT
s_name,
IFNULL(s1.s_score, 0) AS "语文成绩",
IFNULL(s2.s_score, 0) AS "数学成绩",
IFNULL(s3.s_score, 0) AS "英语成绩",
IFNULL(ROUND(AVG(s4.s_score), 2), 0) AS "平均成绩"
FROM
student
LEFT JOIN
score AS s1
ON
s1.s_id = student.s_id AND s1.c_id = 1
LEFT JOIN
score AS s2
ON
s2.s_id = student.s_id AND s2.c_id = 2
LEFT JOIN
score AS s3
ON
s3.s_id = student.s_id AND s3.c_id = 3
LEFT JOIN
score AS s4
ON
s4.s_id = student.s_id
GROUP BY
student.s_id
ORDER BY
AVG(s4.s_score) DESC;
# 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。
# 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90。
SELECT
course.c_id AS "课程ID",
c_name AS "课程名称",
MAX(s_score) AS "最高分",
MIN(s_score) AS "最低分",
AVG(s_score) AS "平均分",
(SUM(IF(s_score >= 60, 1, 0)) / COUNT(1)) AS "及格率",
(SUM(IF(s_score >= 70 AND s_score < 80, 1, 0)) / COUNT(1)) AS "中等率",
(SUM(IF(s_score >= 80 AND s_score < 90, 1, 0)) / COUNT(1)) AS "优良率",
(SUM(IF(s_score >= 90, 1, 0)) / COUNT(1)) AS "优秀率"
FROM
course
INNER JOIN
score
ON
score.c_id = course.c_id
GROUP BY
course.c_id;
# 19.按各科成绩进行排序，并显示排名。
SELECT
c_name AS "课程名称",
s_score AS "分数",
(
CASE
WHEN @p = s_score THEN @rank
WHEN @p := s_score THEN @rank := @rank + 1
END
) AS "排名"
FROM
(
SELECT
c_name,
s_score
FROM
course
INNER JOIN
score
ON
score.c_id = course.c_id
WHERE
course.c_id = 1
ORDER BY
s_score DESC
) AS tab,
(SELECT @rank := 0, @p := NULL) AS r
UNION ALL
SELECT
c_name AS "课程名称",
s_score AS "分数",
(
CASE
WHEN @p2 = s_score THEN @rank2
WHEN @p2 := s_score THEN @rank2 := @rank2 + 1
END
) AS "排名"
FROM
(
SELECT
c_name,
s_score
FROM
course
INNER JOIN
score
ON
score.c_id = course.c_id
WHERE
course.c_id = 2
ORDER BY
s_score DESC
) AS tab,
(SELECT @rank2 := 0, @p2 := NULL) AS r
UNION ALL
SELECT
c_name AS "课程名称",
s_score AS "分数",
(
CASE
WHEN @p3 = s_score THEN @rank3
WHEN @p3 := s_score THEN @rank3 := @rank3 + 1
END
) AS "排名"
FROM
(
SELECT
c_name,
s_score
FROM
course
INNER JOIN
score
ON
score.c_id = course.c_id
WHERE
course.c_id = 3
ORDER BY
s_score DESC
) AS tab,
(SELECT @rank3 := 0, @p3 := NULL) AS r;
# 20.查询学生的总成绩并进行排名。
SELECT
s_name AS "学生姓名",
IFNULL(SUM(s_score), 0) AS "总成绩"
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
GROUP BY
student.s_id
ORDER BY
SUM(s_score) DESC;
# 21.查询不同老师所教不同课程平均分从高到低显示。
SELECT
teacher.t_name AS "老师姓名",
c_name AS "课程名称",
AVG(s_score) AS "平均分"
FROM
teacher
INNER JOIN
course
ON
course.t_id = teacher.t_id
INNER JOIN
score
ON
score.c_id = course.c_id
GROUP BY
course.c_id
ORDER BY
AVG(s_score) DESC;
# 22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩。
(SELECT
c_name AS "课程名称",
student.*,
s_score AS "分数"
FROM
student
INNER JOIN
score
ON
student.s_id = score.s_id
INNER JOIN
course
ON
course.c_id = score.c_id AND course.c_id = 1
ORDER BY
s_score DESC
LIMIT 1, 2)
UNION ALL
(SELECT
c_name AS "课程名称",
student.*,
s_score AS "分数"
FROM
student
INNER JOIN
score
ON
student.s_id = score.s_id
INNER JOIN
course
ON
course.c_id = score.c_id AND course.c_id = 2
ORDER BY
s_score DESC
LIMIT 1, 2)
UNION ALL
(SELECT
c_name AS "课程名称",
student.*,
s_score AS "分数"
FROM
student
INNER JOIN
score
ON
student.s_id = score.s_id
INNER JOIN
course
ON
course.c_id = score.c_id AND course.c_id = 3
ORDER BY
s_score DESC
LIMIT 1, 2);
# 23.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[0-60]及所占百分比。
SELECT
course.c_id AS "课程编号",
c_name AS "课程名称",
SUM(IF(s_score > 85 AND s_score <= 100, 1, 0)) AS "[85-100]人数",
(SUM(IF(s_score > 85 AND s_score <= 100, 1, 0)) / COUNT(1)) AS "所百分比",
SUM(IF(s_score > 70 AND s_score <= 85, 1, 0)) AS "[70-85]人数",
(SUM(IF(s_score > 70 AND s_score <= 85, 1, 0)) / COUNT(1)) AS "所百分比",
SUM(IF(s_score > 60 AND s_score <= 70, 1, 0)) AS "[60-70]人数",
(SUM(IF(s_score > 60 AND s_score <= 70, 1, 0)) / COUNT(1)) AS "所百分比",
SUM(IF(s_score <= 60, 1, 0)) AS "[0-60]人数",
(SUM(IF(s_score <= 60, 1, 0)) / COUNT(1)) AS "所百分比"
FROM
score
INNER JOIN
course
ON
score.c_id = course.c_id
GROUP BY
course.c_id;
# 24.查询学生平均成绩及其名次。
SELECT
s_name AS "学生姓名",
avg AS "平均分",
(
CASE
WHEN @p = avg THEN @rank
WHEN @p := avg THEN @rank := @rank + 1
END
) AS "排名"
FROM
(
SELECT
s_name,
ROUND(IFNULL(AVG(s_score), 0), 2) AS avg
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
GROUP BY
student.s_id
ORDER BY
AVG(s_score) DESC
) AS tab,
(SELECT @rank := 0, @p := NULL) AS r;
# 25.查询各科成绩前三名的记录。
(SELECT
c_name AS "课程名称",
s_score AS "分数"
FROM
score
INNER JOIN
course
ON
course.c_id = score.c_id AND course.c_id = 1
ORDER BY
s_score DESC
LIMIT 3)
UNION ALL
(SELECT
c_name AS "课程名称",
s_score AS "分数"
FROM
score
INNER JOIN
course
ON
course.c_id = score.c_id AND course.c_id = 2
ORDER BY
s_score DESC
LIMIT 3)
UNION ALL
(SELECT
c_name AS "课程名称",
s_score AS "分数"
FROM
score
INNER JOIN
course
ON
course.c_id = score.c_id AND course.c_id = 3
ORDER BY
s_score DESC
LIMIT 3)
# 26.查询每门课程被选修的学生数。
SELECT
c_name AS "课程名称",
COUNT(s_id) AS "人数"
FROM
course
INNER JOIN
score
ON
score.c_id = course.c_id
GROUP BY
course.c_id;
# 27.查询出只有两门课程的全部学生的学号和姓名。
SELECT
student.s_id AS "学号",
s_name AS "姓名"
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
GROUP BY
student.s_id
HAVING
COUNT(score.c_id) = 2;
# 28.查询男生、女生人数。
SELECT
s_sex AS "性别",
COUNT(s_sex) AS "人数"
FROM
student
GROUP BY
s_sex;
# 29.查询名字中含有"风"字的学生信息。
SELECT
*
FROM
student
WHERE
s_name LIKE "%风%";
# 30.查询同名同性学生名单，并统计同名人数。
SELECT
s_name AS "姓名",
s_sex AS "性别",
COUNT(s_name) AS "人数"
FROM
student
GROUP BY
s_name,
s_sex
HAVING
COUNT(s_name) > 1;
# 31.查询1990年出生的学生名单。
SELECT
s_name AS "姓名"
FROM
student
WHERE
s_birth LIKE "1990%";
# 32.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。
SELECT
course.c_id AS "课程编号",
c_name AS "课程名称",
ROUND(AVG(s_score), 2) AS "平均成绩"
FROM
score
INNER JOIN
course
ON
course.c_id = score.c_id
GROUP BY
course.c_id
ORDER BY
AVG(s_score) DESC,
course.c_id ASC;
# 33.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。
SELECT
student.s_id AS "编号",
s_name AS "姓名",
ROUND(AVG(s_score), 2) AS "平均成绩"
FROM
student
LEFT JOIN
score
ON
student.s_id = score.s_id
GROUP BY
student.s_id
HAVING
AVG(s_score) >= 85;
# 34.查询课程名称为"数学"，且分数低于60的学生姓名和分数。
SELECT
s_name AS "学生姓名",
s_score AS "分数"
FROM
student
INNER JOIN
score
ON
student.s_id = score.s_id
INNER JOIN
course
ON
course.c_id = score.c_id AND c_name = "数学"
WHERE
s_score < 60;
# 35.查询所有学生的课程及分数情况。
SELECT
student.s_id AS "学号",
s_name AS "姓名",
c_name AS "课程名称",
IFNULL(s_score, 0) AS "分数"
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
LEFT JOIN
course
ON
course.c_id = score.c_id;
# 36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数。
SELECT
s_name AS "姓名",
c_name AS "课程名称",
s_score AS "分数"
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
LEFT JOIN
course
ON
course.c_id = score.c_id
WHERE
s_score > 70;
# 37.查询不及格的课程。
SELECT
c_name AS "课程名称",
s_score AS "分数"
FROM
course
INNER JOIN
score
ON
score.c_id = course.c_id
WHERE
s_score < 60;
# 38.查询课程编号为"1程成绩在80分以上的学生的学号和姓名。
SELECT
student.s_id AS "学号",
s_name AS "姓名"
FROM
student
INNER JOIN
score
ON
score.s_id = student.s_id AND c_id = 1 AND s_score >= 80;
# 39.求每门课程的学生人数。
SELECT
c_name AS "课程名称",
COUNT(s_id) AS "学生人数"
FROM
course
INNER JOIN
score
ON
score.c_id = course.c_id
GROUP BY
course.c_id;
# 40.查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩。
SELECT
student.*,
s_score AS "分数"
FROM
student
INNER JOIN
score
ON
score.s_id = student.s_id
INNER JOIN
course
ON
course.c_id = score.c_id
INNER JOIN
teacher
ON
teacher.t_id = course.t_id
WHERE
t_name = "张三"
ORDER BY
s_score DESC
LIMIT 1;
# 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩。
SELECT
student.s_id AS "学生学号",
s_name AS "学生姓名",
s1.c_id AS "课程编号",
s1.s_score AS "分数"
FROM
student
LEFT JOIN
score AS s1
ON
s1.s_id = student.s_id
LEFT JOIN
course AS c1
ON
c1.c_id = s1.c_id
WHERE
(
SELECT
COUNT(1)
FROM
student
LEFT JOIN
score AS s2
ON
s2.s_id = student.s_id
LEFT JOIN
course AS c2
ON
c2.c_id = s2.c_id
WHERE
s1.s_score = s2.s_score
AND
c1.c_id != c2.c_id
) > 1;
# 42.查询每门功成绩最好的前两名。
(
SELECT
c_name AS "课程名称",
s_name AS "学生姓名",
s_score AS "分数"
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
LEFT JOIN
course
ON
course.c_id = score.c_id
WHERE
course.c_id = 1
LIMIT 2
)
UNION ALL
(
SELECT
c_name AS "课程名称",
s_name AS "学生姓名",
s_score AS "分数"
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
LEFT JOIN
course
ON
course.c_id = score.c_id
WHERE
course.c_id = 2
LIMIT 2
)
UNION ALL
(
SELECT
c_name AS "课程名称",
s_name AS "学生姓名",
s_score AS "分数"
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
LEFT JOIN
course
ON
course.c_id = score.c_id
WHERE
course.c_id = 3
LIMIT 2
);
# 43.统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
SELECT
c_id AS "课程编号",
COUNT(s_score) AS "学生人数"
FROM
score
GROUP BY
c_id
HAVING
COUNT(s_score) > 5
ORDER BY
COUNT(s_score) DESC,
c_id ASC;
# 44.检索至少选修两门课程的学生学号。
SELECT
s_id AS "学号"
FROM
score
GROUP BY
s_id
HAVING
COUNT(c_id) >= 2;
# 45.查询选修了全部课程的学生信息。
SELECT
student.*
FROM
student
LEFT JOIN
score
ON
score.s_id = student.s_id
GROUP BY
student.s_id
HAVING
COUNT(c_id) = (
SELECT
COUNT(1)
FROM
course
);
# 46.查询各学生的年龄。
SELECT
s_name AS "姓名",
TIMESTAMPDIFF(YEAR, s_birth, NOW())
FROM
student;
# 47.查询本周过生日的学生。
SELECT
student.*
FROM
student
WHERE
WEEK(NOW(), 1) = WEEK(CONCAT(YEAR(s_birth), '-', MONTH(s_birth), '-', DAY(s_birth)), 1);
# 48.查询下周过生日的学生。
SELECT
student.*
FROM
student
WHERE
WEEK(NOW(), 1) = WEEK(CONCAT(YEAR(s_birth), '-', MONTH(s_birth), '-', DAY(s_birth)), 1) - 1;
# 49.查询本月过生日的学生。
SELECT
student.*
FROM
student
WHERE
MONTH(NOW()) = MONTH(CONCAT(YEAR(s_birth), '-', MONTH(s_birth), '-', DAY(s_birth)));
# 50.查询下月过生日的学生。
SELECT
student.*
FROM
student
WHERE
MONTH(NOW()) = MONTH(CONCAT(YEAR(s_birth), '-', MONTH(s_birth), '-', DAY(s_birth))) - 1;
如有问题或有更优写法，欢迎大家留言提建议！