1.题目介绍
21.统计学生成绩等级
有一个学生表
id、学号唯一,学生姓名可能相同,dr代表逻辑删除。
有一个学生各科成绩表
student_id对应学生id,考试为数学、语文、英语,成绩为百分制,dr代表逻辑删除。
请计算各个学生的考试总分(不含删除的学生和成绩)和成绩等级,并按学号从小到大输出。
总分300分,成绩等级标准如下:
A: >=280
B: >=250且<280
C:>=220且<250
D:>= 180且<220
E:<180
最后结果
时间限制:C/C++ 4秒,其他语言8秒
空间限制:C/C++ 256M,其他语言512M
示例1
输入例子:
drop table if exists student;
create table
student
(
id bigint(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
code VARCHAR(20) NOT NULL COMMENT '学生编码',
name VARCHAR(20) NOT NULL COMMENT '学生名称',
dr bigint(1) DEFAULT 0 COMMENT '逻辑删除标志',
PRIMARY KEY (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO student VALUES (1, '001', '张军', 0);
INSERT INTO student VALUES (2, '002', '李浩', 0);
INSERT INTO student VALUES (3, '003', '王明宇', 1);
INSERT INTO student VALUES (4, '004', '秦成', 0);
INSERT INTO student VALUES (5, '005', '李浩', 0);
drop table if exists exam;
create table
exam
(
id bigint(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
student_id bigint(64) NOT NULL COMMENT '学生id',
course VARCHAR(100) NOT NULL COMMENT '课程',
score SMALLINT NOT NULL COMMENT '分数',
dr bigint(1) DEFAULT 0 COMMENT '逻辑删除标志',
PRIMARY KEY (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO exam VALUES (1, 1, '数学', '94',0);
INSERT INTO exam VALUES (2, 1, '语文', '92',0);
INSERT INTO exam VALUES (3, 1, '英语', '96',0);
INSERT INTO exam VALUES (4, 2, '数学', '82',0);
INSERT INTO exam VALUES (5, 2, '语文', '76',0);
INSERT INTO exam VALUES (6, 2, '英语', '72',0);
INSERT INTO exam VALUES (7, 3, '数学', '52',1);
INSERT INTO exam VALUES (8, 3, '语文', '59',1);
INSERT INTO exam VALUES (9, 3, '英语', '43',1);
INSERT INTO exam VALUES (10, 4, '数学', '88',0);
INSERT INTO exam VALUES (11, 4, '语文', '82',0);
INSERT INTO exam VALUES (12, 4, '英语', '83',0);
INSERT INTO exam VALUES (13, 5, '数学', '90',0);
INSERT INTO exam VALUES (14, 5, '语文', '92',0);
INSERT INTO exam VALUES (15, 5, '英语', '93',0);
输出例子:
001|张军|282|A
002|李浩|230|C
004|秦成|253|B
005|李浩|275|B
2.代码
2.1 使用if嵌套
思路
注意这里totalscore是一个计算列,而不能直接在if语句中引用,要多次使用sum(score)
select s.code, s.name, sum(score) as totalscore,
if(sum(score) >= 280, 'A',
if(sum(score) >= 250,'B',
if(sum(score) >= 220,'C',
if(sum(score) >= 180,'D','E')))) as level
from student s left join exam e
on s.id = e.student_id
where s.dr <> 1
group by s.id
order by s.code
2.2 使用case when
思路
SELECT
s.code,
s.name,
SUM(score) AS totalscore,
CASE
WHEN SUM(score) >= 280 THEN 'A'
WHEN SUM(score) >= 250 THEN 'B'
WHEN SUM(score) >= 220 THEN 'C'
WHEN SUM(score) >= 180 THEN 'D'
ELSE 'E'
END AS level
FROM
student s
LEFT JOIN exam e ON s.id = e.student_id
WHERE
s.dr <> 1
GROUP BY
s.id
ORDER BY
s.code;
标签:INSERT,21,INTO,VALUES,student,成绩,id,等级,exam
From: https://www.cnblogs.com/trmbh12/p/17917383.html