sql 查询出各科成绩、平均成绩，总成绩，根据总成绩分数相同，排名相同 |
您所在的位置：网站首页 › excel分组平均成绩要差不多多少 › sql 查询出各科成绩、平均成绩，总成绩，根据总成绩分数相同，排名相同 |
sql 查询出各科成绩、平均成绩，总成绩，根据总成绩分数相同，排名相同
|
今天面试被sql绊倒了，废话少说，希望以后面试时不再填坑。需求基本和标题一样。 三张表：学生表 student，科目表：course，成绩表：grade sql语句如下(已优化)，需要当前数据库格式为utf-8 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 case when，这里用到case when语法 知道case when用法，就理解下以上根据用户分组：group by s.student_id ，算出语数外总成绩 select s.student_id as '编号',s.name as '姓名',s.age as '年龄', sum(score) as '语数外总分数' 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变量以及赋值 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解析一下 首先开始时候 @gnum表示上个总成绩变量 初始化=0， 然后判断是否和上个total相同 相同的话，把上个排名@rownnum赋值给当前列排名，不同则@rownum+1 查询结果如下: |
【本文地址】
今日新闻 |
推荐新闻 |