今天面试被这道MySQL题难住了！

vlambda
2020-10-15

今天面试被这道MySQL题难住了！

视频教程

面试中面试官问你啥问题，小编都给你整理在这里了。每天都会整理一份最接地气的面试题，希望能帮助到你！今天面试被这道MySQL题难住了！

获取实战视频教程的，可以到这个网站里获取【http://www.mano100.cn/】，只需158RMB，即可查看与下载全站视频教程今天面试被这道MySQL题难住了！

文章内容

今天面试被sql绊倒了，，希望以后面试时不再填坑。需求基本和标题一样。

三张表：学生表 student，科目表：course，成绩表：grade

要求按照学生展示该生的各科成绩、总成绩、平均分，并按照总分进行排序，效果如下：

今天面试被这道MySQL题难住了！

sql语句如下

CREATE TABLE `course` (
  `course_id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(64) NOT NULL,
  PRIMARY KEY (`course_id`)
)
INSERT INTO `course` VALUES ('1', '语文');
INSERT INTO `course` VALUES ('2', '数学');
INSERT INTO `course` VALUES ('3', '外语');


CREATE TABLE `grade` (
  `grade_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `score` decimal(5,2) NOT NULL,
  PRIMARY KEY (`grade_id`)
)

INSERT INTO `grade` VALUES ('1', '1', '1', '83.00');
INSERT INTO `grade` VALUES ('2', '1', '2', '75.00');
INSERT INTO `grade` VALUES ('3', '1', '3', '59.00');
INSERT INTO `grade` VALUES ('4', '2', '1', '76.00');
INSERT INTO `grade` VALUES ('5', '2', '2', '95.00');
INSERT INTO `grade` VALUES ('6', '2', '3', '87.00');
INSERT INTO `grade` VALUES ('7', '3', '1', '89.00');
INSERT INTO `grade` VALUES ('8', '3', '2', '74.00');
INSERT INTO `grade` VALUES ('9', '3', '3', '58.00');
INSERT INTO `grade` VALUES ('10', '4', '1', '95.00');
INSERT INTO `grade` VALUES ('11', '4', '2', '76.00');
INSERT INTO `grade` VALUES ('12', '4', '3', '87.00');


CREATE TABLE `student` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
)

INSERT INTO `student` VALUES ('1', '张三', '18');
INSERT INTO `student` VALUES ('2', '李四', '18');
INSERT INTO `student` VALUES ('3', '王五', '18');
INSERT INTO `student` VALUES ('4', '赵柳', '18');

首先行变列查询出其中的各科成绩以及平均成绩、总成绩：

select s.student_id as '编号',s.name as '姓名',s.age as '年龄',
sum(case c_name when '语文' then score else 0 end) as '语文',
sum(case c_name when '数学' then score else 0 end) as '数学',
sum(case c_name when '外语' then score else 0 end) as '外语',
convert(sum(score)/3,decimal(5,2)) as '平均成绩',
convert(sum(score),decimal(5,2)) as total
from student s,course c,grade g where s.student_id=g.student_id and c.course_id=g.course_id
group by s.student_id order by sum(score) desc

然后需要查出排名，而且总分相同的排名相同，好的，此时运用到了sql变量以及赋值

sql变量用@来表示，赋值用:=来实现；

select
@rows:=@rows+1 as rows,
if(@gnum=total,@rownum:=@rownum,@rownum:=@rownum+1) as rank,
@gnum:=total,
 message.* from(
select s.student_id as '编号',s.name as '姓名',s.age as '年龄',
sum(case c_name when '语文' then score else 0 end) as '语文',
sum(case c_name when '数学' then score else 0 end) as '数学',
sum(case c_name when '外语' then score else 0 end) as '外语',
convert(sum(score)/3,decimal(5,2)) as '平均成绩',
convert(sum(score),decimal(5,2)) as total
from student s,course c,grade g where s.student_id=g.student_id and c.course_id=g.course_id
group by s.student_id order by sum(score) desc) message,(select @rownum:=0,@gnum:=0,@rows:=0) number