21.统计学生成绩等级

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;