前幾天去了兩個比較牛的互聯網公司面試,在sql這塊都遇到問題了,哎,可惜呀,先把簡單的梳理一下
成績表 score
按某一個維度進行分組
例如:
求每個同學的總分
SELECT student,SUM(score) FROM score GROUP BY student
求每個同學的平均分
SELECT student,AVG(score) FROM score GROUP BY student
也可以按照 班級,課程 來求
查出掛了兩門及以上的學生
SELECT student,SUM(score<60)as gk FROM score GROUP BY student HAVING gk>1
(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<a_score
先寫到這吧