Home >Database >Mysql Tutorial >mysql常用查询:groupby,左连接,子查询,havingwhere_MySQL
前几天去了两个比较牛的互联网公司面试,在sql这块都遇到问题了,哎,可惜呀,先把简单的梳理一下
成绩表 score
按某一个维度进行分组
例如:
求每个同学的总分
SELECT student,SUM(score) FROM score GROUP BY student
求每个同学的平均分
SELECT student,AVG(score) FROM score GROUP BY student
也可以按照 班级,课程 来求
查出挂了两门及以上的学生
SELECT student,SUM(score1
(1)where子查询
(把内层查询结果当作外层查询的比较条件)
求比每门课程平均分低的学生
SELECT student ,course, score 先写到这吧
FROM score ,(SELECT course AS a_course,AVG( score)AS a_score FROM score GROUP BY course) AS avg_score
WHERE course = a_course AND score