50道SQL面试题

50道SQL面试题

有需要互关的小伙伴,关注一下,有关必回关,争取今年认证早日拿到博客专家

环境

-- ----------------------------
-- 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');


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144

值得关注的

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;
1

---

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='数学老师-杰斯');
1

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 = '数学老师-杰斯');
1

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);
1

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);
1

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);
1

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));
1

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);
1

---

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;
1

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 = '数学老师-杰斯');
1

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);
1

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

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