SQL面试题

题目描述

学生表（STUDENT）

ID(pk)	STUDENT_NO	STUDENT_NAME
1	1001	王明
2	1002	李涛
3	1003	刘雨
4	1004	赵欢

成绩表（SCORE）

ID(pk)	STUDENT_ID(fk)	CLASS_NAME	SCORE
1	1	语文	90
2	1	数学	90
3	1	英语	90
4	2	语文	60
5	2	数学	60
6	2	英语	60
7	3	语文	70
8	3	数学	70
9	3	英语	70
10	4	语文	80
11	4	数学	80
12	4	英语	80

学生表主键（ID）对应 成绩表外键(STUDENT_ID)
请使用SQL统计出总分数超过200的学生,并按总分数进行排名

输出结果格式如下

STUDENT_NO	STUDENT_NAME	TOTAL_SCORE
1001	王明	270
1004	赵欢	240
1003	刘雨	210

---

SQL解答

SELECT stu.student_no, stu.student_name ,SUM(sc.score) total_score

FROM student stu 

LEFT JOIN score sc ON stu.id= sc.student_id 

GROUP BY stu.student_no,stu.student_name HAVING total_score>200

ORDER BY total_score desc;

这样的一道SQL题，融合了 LEFT JOIN、GROUP BY、HAVING、ORDER BY、DESC等，算是比较综合的一道题目

其中考察点是主键与外键的关联(外连接)、分组之后的过滤（GROUP BY、HAVING、对结果排序（升序降序）

---

易错点

易错点1

GROUP BY stu.student_no

如果SQL写成如下

SELECT stu.student_no, stu.student_name ,SUM(sc.score) total_score

FROM student stu 

LEFT JOIN score sc ON stu.id= sc.student_id 

GROUP BY stu.student_no HAVING total_score>200

ORDER BY total_score desc;

报错信息如下：

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘demo.stu.student_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, Time: 0.001000s

原因是分组时，需要使用

GROUP BY stu.student_no,stu.student_name

---

易错点2

WHERE total_score>200

如果SQL写成如下

SELECT stu.student_no, stu.student_name ,SUM(sc.score) total_score

FROM student stu 

LEFT JOIN score sc ON stu.id= sc.student_id WHERE total_score>200

GROUP BY stu.student_no,stu.student_name

ORDER BY total_score desc;

总分数超过200这个条件是基于分组之后的条件，很多人把这个条件放到了 where 子句之后，也是不对的

报错信息如下：

1054 - Unknown column ‘total_score’ in ‘where clause’, Time: 0.000000s