今天面试被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
查询结果如下: