50道sql面试题
有需要互关的小伙伴,关注一下,有关必回关,争取今年认证早日拿到博客专家
环境
mysql">-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`
(
`t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB
CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci
ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '数学老师-杰斯');
INSERT INTO `teacher` VALUES ('02', '语文老师-盲僧');
INSERT INTO `teacher` VALUES ('03', '英语老师-菲欧娜');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`
(
`s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`s_birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`s_sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB
CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci
ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '流浪法师-瑞兹', '1990-01-01', '男');
INSERT INTO `student` VALUES ('02', '探险家-EZ', '1990-12-21', '男');
INSERT INTO `student` VALUES ('03', '疾风剑豪-亚瑟', '1990-05-20', '男');
INSERT INTO `student` VALUES ('04', '迅捷斥候-提莫', '1990-08-06', '男');
INSERT INTO `student` VALUES ('05', '黑暗之女-安妮', '1991-12-01', '女');
INSERT INTO `student` VALUES ('06', '战争女神-希维尔', '1992-03-01', '女');
INSERT INTO `student` VALUES ('07', '光辉女郎-拉克丝', '1989-07-01', '女');
INSERT INTO `student` VALUES ('08', '放逐之刃-锐雯', '1990-01-20', '女');
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`
(
`s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`s_score` int(0) NULL DEFAULT NULL,
PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB
CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci
ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('01', '01', 80);
INSERT INTO `score` VALUES ('01', '02', 90);
INSERT INTO `score` VALUES ('01', '03', 99);
INSERT INTO `score` VALUES ('02', '01', 70);
INSERT INTO `score` VALUES ('02', '02', 60);
INSERT INTO `score` VALUES ('02', '03', 80);
INSERT INTO `score` VALUES ('03', '01', 80);
INSERT INTO `score` VALUES ('03', '02', 80);
INSERT INTO `score` VALUES ('03', '03', 80);
INSERT INTO `score` VALUES ('04', '01', 50);
INSERT INTO `score` VALUES ('04', '02', 30);
INSERT INTO `score` VALUES ('04', '03', 20);
INSERT INTO `score` VALUES ('05', '01', 76);
INSERT INTO `score` VALUES ('05', '02', 87);
INSERT INTO `score` VALUES ('06', '01', 31);
INSERT INTO `score` VALUES ('06', '03', 34);
INSERT INTO `score` VALUES ('07', '02', 89);
INSERT INTO `score` VALUES ('07', '03', 98);
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`
(
`c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`c_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB
CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci
ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '01');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');
50道SQL面试题
-- 50道SQL面试题
-- 1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（难）
select c1.s_id from (select * from score where c_id=01) c1 JOIN (select * from score where c_id=02) c2 on c1.s_id = c2.S_id where c1.s_score>c2.s_score;
-- 2、查询平均成绩大于60分的学生的学号和平均成绩(凑数的,分组统计having过滤)
select s_id,AVG(s_score) avg_score from score GROUP BY s_id HAVING AVG(s_score)>60 ORDER BY avg_score desc;
-- 3、查询所有学生的学号、姓名、选课数、总成绩(凑数的,分组统计)
select s.s_id,s_name,count(sc.c_id),sum(sc.s_score) from student s left join score sc on s.s_id=sc.s_id GROUP BY s.s_id,s_name;
-- 4、查询姓“猴”的老师的个数(纯凑数的)
select count(*) FROM teacher where t_name like '%杰%';
-- 5、查询没学过“数学老师-杰斯”老师课的学生的学号、姓名(凑数的)
select distinct s.s_id,s.s_name from student s left join score sc on s.s_id = sc.s_id where sc.c_id not in (select co.c_id FROM teacher t left join course co on t.t_id=co.t_id where t_name = '数学老师-杰斯');
-- 6、查询学过“数学老师-杰斯”老师所教的所有课的同学的学号、姓名(有意思,所有课程,一个`查询结果`和另一个`查询结果`有某种函数关系,注意不再是查询条件,是结果集之间的对比)
select s.s_id,s.s_name from student s left join score sc on s.s_id=sc.s_id and sc.c_id in (select co.c_id from teacher t join course co on t.t_id=co.t_id where t_name = '数学老师-杰斯') group by s.s_id,s.s_name HAVING count(*) = (select count(*) from teacher t join course co on t.t_id=co.t_id where t_name = '数学老师-杰斯');
-- 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名(和6是同意一类型的需要一丢丢思路)
select s.s_id,s_name from student s left join score sc1 on s.s_id=sc1.s_id join score sc2 on sc1.s_id = sc2.s_id where sc1.c_id = 01 and sc2.c_id = 02;
select s_id,s_name from student where s_id in (select s1.s_id from score s1 join score s2 on s1.s_id=s2.s_id where s1.c_id = 01 and s2.c_id = 02);
select s_id,s_name from student where s_id in (select s_id from score where c_id in(01,02) group by s_id having count(c_id) = 2);
-- 8、查询课程编号为“02”的总成绩(简单)
select sum(s_score) from score where c_id = 02;
-- 9、查询所有课程成绩都小于60分的学生的学号、姓名(有意思,如果不用max可能难度不小)
select s.s_id,s_name from student s left join score sc on s.s_id=sc.s_id group by s.s_id,s_name having max(sc.s_score)<60;
-- 10、查询没有学全所有课的学生的学号、姓名
select s.s_id,s_name from student s left join score sc on s.s_id=sc.s_id group by s.s_id,s_name having count(*)<(SELECT count(*) from course);
-- 11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名 （凑数的）
select distinct s.s_id,s_name from student s join score sc on s.s_id=sc.s_id where sc.c_id in (select c_id from score where s_id = 01) and s.s_id != 01;
-- 12、查询和“01”号同学所学课程完全相同的其他同学的学号（数据集完全相同 与6,7同类型）
select s_id from score where c_id in (select c_id from score where s_id = 01) and s_id != 01 group by s_id having count(c_id) = (select count(*) from score where s_id = 01);
select s.s_id from (select c_id from score where s_id = 01) t left join score sc on t.c_id=sc.c_id left join student s on sc.s_id=s.s_id where s.s_id!=01 group by s.s_id,s_name having count(*)= (select count(*)from score where s_id=01);
-- 13、查询没学过"数学老师-杰斯"老师讲授的任一门课程的学生姓名(有意思 逆向思维更简单,先查出学过的,包括学了部分的和全部的,在用not in)
SELECT s_name from student stu2 where stu2.s_id not in (select DISTINCT stu.s_id from student stu,score sc,course c,teacher t where stu.s_id=sc.s_id and sc.c_id =c.c_id and c.t_id = t.t_id and t.t_name = '数学老师-杰斯') ;
# 显然相比第一种方法麻烦很多
select s_id,s_name from student where s_id not in (select s.s_id from (select co.c_id FROM teacher t left join course co on t.t_id=co.t_id where t_name = '数学老师-杰斯') t left join score sc on t.c_id=sc.c_id left join student s on sc.s_id=s.s_id group by s.s_id);
-- 14、空
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩(凑数的)
select s.s_id,s_name,t.avg_score from student s join (select s_id,avg(s_score) avg_score from score where s_score <60 group by s_id having count(*)>=2) t on s.s_id = t.s_id;
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息(纯凑数的)
select s.* from student s left join score sc on s.s_id=sc.s_id where sc.c_id = 01 and sc.s_score<60 ORDER BY sc.s_score desc;
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(注意一下并不是所有人都选择了课)
select s.s_name,sc.*,t.avg_score from student s left join score sc on s.s_id=sc.s_id join (select s.s_id ,avg(sc.s_score) avg_score from student s left join score sc on s.s_id=sc.s_id group by s.s_id ) t on s.s_id=t.s_id order by t.avg_score desc;
# 没选课的会丢失
select s.s_name,sc.*,t.avg_score from student s left join score sc on s.s_id=sc.s_id join (select s_id ,avg(s_score) avg_score from score group by s_id) t on s.s_id=t.s_id order by t.avg_score desc;
-- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率(就是麻烦一点)
select t.*,c.c_name,concat((count_jige/count_sid)*100,'%') as 及格率 from course c join (select c_id,max(s_score) max_score,min(s_score) min_score,avg(s_score) avg_score,count(s_id) count_sid from score group by c_id) t on c.c_id = t.c_id join(select c_id,count(s_id) count_jige from score where s_score >=60 group by c_id) t2 on c.c_id= t2.c_id;
-- 19、查询学生的总成绩并进行排名(凑数的)
select s.s_id,s.s_name,ifnull(sum(s_score),0) from student s left join score sc on s.s_id=sc.s_id group by s.s_id order by sum(s_score) desc;
-- 20、查询不同老师所教不同课程平均分,从高到低显示(凑数的)
select t.t_name,c.c_name,ifnull(avg(s.s_score),0) from teacher t left join course c on t.t_id = c.t_id left join score s on c.c_id = s.c_id GROUP BY t.t_name,c.c_name ORDER BY avg(s.s_score) desc;
-- 21、查询学生平均成绩及其名次(排名字段怎么搞)
select s_id,avg(s_score) from score group by s_id order by avg(s_score) desc;
-- 22、按各科成绩进行排序，并显示排名(这个题目时个啥意思??)
select c_id,sum(s_score) from score group by c_id order by sum(s_score) desc ;
-- 23、查询每门功课成绩最好的前两名学生姓名(难)
-- 23、查询每门功课成绩最好的前两名学生姓名
select a.s_id,a.s_name,b.num_row
from student a
join (select s_id, c_id, row_number() over(partition by c_id order by s_score desc) num_row from score) b on a.s_id=b.s_id
where b.num_row<=2;
select s_id, c_id, row_number() from score;
SELECT s_id, c_id, RANK() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS num_row
FROM score;
select *,sum(s_score) over() from score;
-- 24、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
-- 25、查询各科成绩前三名的记录（不考虑成绩并列情况）
-- 26、使用分段[100-85),[85-70),[70-60),[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(难,不一定能想到子查询)
# 有问题的
select c_id,count(*) from score where s_score >85 group by c_id
union all
select c_id,count(*) from score where s_score >70 and s_score<=85 group by c_id
union all
select c_id,count(*) from score where s_score >60 and s_score<=70 group by c_id
union all
select c_id,count(*) from score where s_score <=60 group by c_id;
select c_id,count(s_id) from score group by c_id;
select c_id,c_name,
ifnull((select count(*) from score s where c.c_id = s.c_id and s_score >=85 group by s.c_id),0) as '[100-85]',
ifnull((select count(*) from score s where c.c_id = s.c_id and s_score >=70 and s_score < 85 group by s.c_id),0) as '(85-70]',
ifnull((select count(*) from score s where c.c_id = s.c_id and s_score >=60 and s_score < 70 group by s.c_id),0) as '(70-60]',
ifnull((select count(*) from score s where c.c_id = s.c_id and s_score <60 group by s.c_id),0) as '(60,0]'
from course c;
select count(s_id) from score;
-- 27、查询每门课程被选修的学生数(凑数的)
select c_id,count(s_id) from score group by c_id;
-- 28、查询出只有两门课程的全部学生的学号和姓名(比较子查询与连表查询)
explain select s_id,s_name from student where s_id in (select s_id from score group by s_id having count(c_id)=2);
explain select s.s_id,s.s_name from student s join score s2 on s.s_id = s2.s_id group by s.s_id,s.s_name having count(c_id) = 2;
-- 29、查询男生、女生人数(纯凑数的吧)
select s_sex,count(s_id) from student group by s_sex;
-- 30、查询名字中含有"风"字的学生信息(纯凑数的)
select * from student where s_name like '%风%';
-- 31、查询1990年出生的学生名单(注意比较两种思路)
select * from student where s_birth like '1990%';
SELECT * from student where year(s_birth) = '1990';
-- 32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩(虽然是凑数的,让你写复杂了)
# 你写复杂了吧
select s.s_id,s.s_name,t.avg_score from student s join (select s_id,avg(s_score) avg_score from score group by s_id having avg(s_score)>85) t on s.s_id=t.s_id;
# 另一种实现
select sc.s_id,(select s.s_name from student s where s.s_id = sc.s_id) as s_name,avg(s_score) avg_score from score sc group by sc.s_id having avg(sc.s_score)>85;
# 理想的
select s.s_id,s.s_name,avg(s_score) from student s join score s2 on s.s_id = s2.s_id group by s.s_id,s.s_name having avg(s_score) > 85;
-- 33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列(简单-凑数的吧)
select c_id,avg(s_score) avg_score from score group by c_id order by avg_score desc,c_id;
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数(纯凑数的吧)
select student.s_name,s_score from student join score s on student.s_id = s.s_id join course c on s.c_id = c.c_id where c_name = '数学' and s_score <60;
-- 35、查询所有学生的课程及分数情况(简单的多表联查)
select * from student join score s on student.s_id = s.s_id;
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数(简单的多表联查)
select s.s_name,c.c_name,s2.s_score from student s join score s2 on s.s_id = s2.s_id join course c on s2.c_id = c.c_id where s2.s_score>70;
-- 37、查询不及格的课程并按课程号从大到小排列(简单的单表查询),题目的意思至少有一门不及格还是所有都不及格,或者学生与学生不及格的课程
# 至少有一门不及格
select c_id from score group by c_id having min(s_score) <60 order by c_id desc;
# 所有都不及格
select c_id from score group by c_id having max(s_score) <60 order by c_id desc;
# 学生与学生不及格的课程
select * from score where s_score <60 order by c_id desc;
-- 38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名(简单的单表查询)
select s.s_id,s_name from student s join score sc on s.s_id = sc.s_id where c_id=03 and s_score>80;
-- 39、求每门课程的学生人数(简单的统计)
select c_id,count(s_id) from score group by c_id;
-- 40、查询选修“数学老师-杰斯”老师所授课程的学生中成绩最高的学生姓名及其成绩(数学老师可能教了多门课,某一门课的最高成绩可能并列多人)
select s_name,s2.c_id,s2.s_score from student s join score s2 on s.s_id = s2.s_id join (
select sc.c_id,max(sc.s_score) as max_score from score sc join course c on sc.c_id = c.c_id join teacher t on c.t_id = t.t_id where t_name = '数学老师-杰斯' group by sc.c_id) tmp
on s2.c_id = tmp.c_id and s2.s_score = tmp.max_score order by s2.c_id;
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （难）
# 重复数据 1和3 语文 80/3和1语文80
select distinct s.s_id,s2.s_id,s.c_id,s.s_score from score s join score s2 on s.c_id=s2.c_id and s.s_score = s2.s_score and s.s_id != s2.s_id order by c_id;
-- 42、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列(纯凑数的)
select c_id,count(s_id) total from score group by c_id having count(s_id)>5 order by total desc,c_id;
-- 43、检索至少选修两门课程的学生学号(纯凑数的)
select s_id,count(c_id) from score group by s_id having count(c_id)>=2;
-- 44、查询选修了全部课程的学生信息(不难)
select * from student where s_id in (select s_id from score group by s_id having count(c_id)=(select count(*) from course));
select s_id,(select s.s_name from student s where s.s_id=score.s_id) from score group by s_id having count(c_id)=(select count(*) from course);
-- 45、查询各学生的年龄(函数)
select *,(year(curdate())-year(s_birth)) from student;
-- 46、查询两门以上不及格课程的同学的学号及其平均成绩(一般统计)
select s_id,avg(s_score) from score where s_score <60 group by s_id having count(*)>=2;
-- 47、查询本月过生日的学生(函数)
select * from student where month(s_birth) = month(now());
-- 48、查询下一个月过生日的学生(函数)
select * from student where month(s_birth) = month(now())+2;
值得关注的
1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
理解:同一学生的01比02高,例如:张三的01比张三的02高
select s1.s_id,s1.c_id,s1.s_score,s2.c_id,s2.s_score from score s1 join score s2 on s1.s_id = s2.s_id where s1.c_id = 01 and s2.c_id = 02 and s1.s_score>s2.s_score;
6、查询学过“数学老师-杰斯”老师所教的所有课的同学的学号
select s.s_id,s.s_name from student s join score s2 on s.s_id = s2.s_id join course c on s2.c_id = c.c_id join teacher t on c.t_id = t.t_id where t.t_name='数学老师-杰斯' group by s.s_id,s.s_name having count(c.c_id) = (select count(*) from teacher t2 join course c2 on t2.t_id = c2.t_id where t2.t_name='数学老师-杰斯');
select s.s_id,s.s_name from student s join score sc on s.s_id=sc.s_id and sc.c_id in (select co.c_id from teacher t join course co on t.t_id=co.t_id where t_name = '数学老师-杰斯') group by s.s_id,s.s_name HAVING count(*) = (select count(*) from teacher t join course co on t.t_id=co.t_id where t_name = '数学老师-杰斯');
10、查询没有学全所有课的学生的学号、姓名(和6差不多)
select s.s_id,s.s_name from student s join score s2 on s.s_id = s2.s_id group by s.s_id,s.s_name having count(s2.c_id) < (select count(*) from course);
12、查询和“01”号同学所学课程完全相同的其他同学的学号（和6差不多）
select s_id from score where c_id in (select c_id from score where s_id = 01) and s_id != 01 group by s_id having count(c_id) = (select count(*) from score where s_id = 01);
select s.s_id from (select c_id from score where s_id = 01) t join score s on t.c_id=s.c_id where s.s_id!=01 group by s.s_id having count(*)= (select count(*)from score where s_id=01);
44、查询选修了全部课程的学生信息
select * from student where s_id in (select s_id from score group by s_id having count(c_id)=(select count(*) from course));
select s_id,(select s.s_name from student s where s.s_id=score.s_id) from score group by s_id having count(c_id)=(select count(*) from course);
9、查询所有课程成绩都小于60分的学生的学号、姓名(有意思,如果不用max可能难度不小)
select s.s_id,s_name from student s left join score sc on s.s_id=sc.s_id group by s.s_id,s_name having max(sc.s_score)<60;
13、查询没学过"数学老师-杰斯"老师讲授的任一门课程的学生姓名(有意思 逆向思维更简单,先查出学过的,包括学了部分的和全部的,在用not in)
select s_id,s_name from student where s_id not in (select distinct s.s_id from score s join course c on s.c_id = c.c_id join teacher t on c.t_id = t.t_id where t.t_name = '数学老师-杰斯');
select s_id,s_name from student where s_id not in (select s.s_id from score s join course c on s.c_id = c.c_id join teacher t on c.t_id = t.t_id where t.t_name = '数学老师-杰斯' group by s.s_id);
21、查询学生平均成绩及其名次(排名字段怎么搞)
select t1.s_id,t1.avg_score1,(count(t2.avg_score1)+1) 'rank' from (select s_id,avg(s_score) avg_score1 from score group by s_id) t1 left join (select s_id,avg(s_score) avg_score1 from score group by s_id)t2 on t1.avg_score1<t2.avg_score1 group by t1.s_id order by t1.avg_score1 desc;
辅助理解
select * from (select s_id,avg(s_score) avg_score1 from score group by s_id) t1 left join (select s_id,avg(s_score) avg_score1 from score group by s_id)t2 on t1.avg_score1<t2.avg_score1 order by t1.s_id;
+------+------------+------+------------+
| s_id | avg_score1 | s_id | avg_score1 |
+------+------------+------+------------+
| 01 | 89.6667 | 07 | 93.5000 |
| 02 | 70.0000 | 07 | 93.5000 |
| 02 | 70.0000 | 05 | 81.5000 |
| 02 | 70.0000 | 01 | 89.6667 |
| 02 | 70.0000 | 03 | 80.0000 |
| 03 | 80.0000 | 05 | 81.5000 |
| 03 | 80.0000 | 01 | 89.6667 |
| 03 | 80.0000 | 07 | 93.5000 |
| 04 | 33.3333 | 05 | 81.5000 |
| 04 | 33.3333 | 01 | 89.6667 |
| 04 | 33.3333 | 02 | 70.0000 |
| 04 | 33.3333 | 03 | 80.0000 |
| 04 | 33.3333 | 07 | 93.5000 |
| 05 | 81.5000 | 07 | 93.5000 |
| 05 | 81.5000 | 01 | 89.6667 |
| 06 | 32.5000 | 02 | 70.0000 |
| 06 | 32.5000 | 07 | 93.5000 |
| 06 | 32.5000 | 03 | 80.0000 |
| 06 | 32.5000 | 01 | 89.6667 |
| 06 | 32.5000 | 05 | 81.5000 |
| 06 | 32.5000 | 04 | 33.3333 |
| 07 | 93.5000 | NULL | NULL |
+------+------------+------+------------+
26、使用分段[100-60],[0,60)来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(难,不一定能想到子查询)
select c_id,c_name,
ifnull((select count(*) from score s where c.c_id = s.c_id and s_score >=60 group by s.c_id),0) as '[100-60]',
ifnull((select count(*) from score s where c.c_id = s.c_id and s_score <60 group by s.c_id),0) as '(60,0]'
from course c;
40、查询选修“数学老师-杰斯”老师所授课程的学生中成绩最高的学生姓名及其成绩(数学老师可能教了多门课,某一门课的最高成绩可能并列多人)
select s_name,s2.c_id,s2.s_score from student s join score s2 on s.s_id = s2.s_id join (select sc.c_id,max(sc.s_score) as max_score from score sc join course c on sc.c_id = c.c_id join teacher t on c.t_id = t.t_id where t_name = '数学老师-杰斯' group by sc.c_id) tmp on s2.c_id = tmp.c_id and s2.s_score = tmp.max_score order by s2.c_id;
23、查询每门功课成绩最好的前两名学生姓名(难)
select s2.c_id,s.s_id,s.s_name,s2.s_score from student s join score s2 on s.s_id = s2.s_id join (select s.c_id, s.s_id from score s left join score s2 on s.c_id = s2.c_id and s.s_score < s2.s_score group by s.c_id, s.s_id having ifnull(count(s2.s_id),0) <= 1) t on s2.c_id = t.c_id and s2.s_id = t.s_id order by s2.c_id,s2.s_score desc;
+------+------+-----------------+---------+
| c_id | s_id | s_name | s_score |
+------+------+-----------------+---------+
| 01 | 01 | 流浪法师-瑞兹 | 80 |
| 01 | 03 | 疾风剑豪-亚瑟 | 80 |
| 02 | 01 | 流浪法师-瑞兹 | 90 |
| 02 | 07 | 光辉女郎-拉克丝 | 89 |
| 03 | 01 | 流浪法师-瑞兹 | 99 |
| 03 | 07 | 光辉女郎-拉克丝 | 98 |
+------+------+-----------------+---------+
24、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select s2.c_id,s.s_id, s.s_name, s2.s_score from student s join score s2 on s.s_id = s2.s_id join (select s.c_id, s.s_id from score s left join score s2 on s.c_id = s2.c_id and s.s_score < s2.s_score group by s.c_id, s.s_id having ifnull(count(s2.s_id),0) <= 3 and ifnull(count(s2.s_id),0) >= 1) t on s2.c_id = t.c_id and s2.s_id = t.s_id order by s2.c_id,s2.s_score desc;
+------+------+-----------------+---------+
| c_id | s_id | s_name | s_score |
+------+------+-----------------+---------+
| 01 | 05 | 黑暗之女-安妮 | 76 |
| 01 | 02 | 探险家-EZ | 70 |
| 02 | 07 | 光辉女郎-拉克丝 | 89 |
| 02 | 05 | 黑暗之女-安妮 | 87 |
| 02 | 03 | 疾风剑豪-亚瑟 | 80 |
| 03 | 07 | 光辉女郎-拉克丝 | 98 |
| 03 | 02 | 探险家-EZ | 80 |
| 03 | 03 | 疾风剑豪-亚瑟 | 80 |
+------+------+-----------------+---------+
select * from score ORDER BY c_id,s_score desc;
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 01 | 01 | 80 |
| 03 | 01 | 80 |
| 05 | 01 | 76 |
| 02 | 01 | 70 |
| 04 | 01 | 50 |
| 06 | 01 | 31 |
| 01 | 02 | 90 |
| 07 | 02 | 89 |
| 05 | 02 | 87 |
| 03 | 02 | 80 |
| 02 | 02 | 60 |
| 04 | 02 | 30 |
| 01 | 03 | 99 |
| 07 | 03 | 98 |
| 03 | 03 | 80 |
| 02 | 03 | 80 |
| 06 | 03 | 34 |
| 04 | 03 | 20 |
+------+------+---------+
18 rows in set (0.08 sec)
函数相关
-- 45、查询各学生的年龄(函数)
select *,(year(curdate())-year(s_birth)) from student;
-- 46、查询两门以上不及格课程的同学的学号及其平均成绩(一般统计)
select s_id,avg(s_score) from score where s_score <60 group by s_id having count(*)>=2;
-- 47、查询本月过生日的学生(函数)
select * from student where month(s_birth) = month(now());
-- 48、查询下一个月过生日的学生(函数)
select * from student where month(s_birth) = month(now())+2;
