带GROUP BY的查询，比如：

SELECT subject, AVG(score) FROM student_score GROUP BY subejct;
SELECT subject, AVG(score) FROM student_score WHERE score >= 60 GROUP BY subject;

注意，带分组的查询，查询列中应该避免包含非分组列的情况，比如如果在上面的SELECT中增加学号列，则结果就是不正确的。

MySQL提供了一个ONLY_FULL_GROUP_BY模式，如果开启了这个模式，则不允许将非分组列放在查询列表中。


带过滤条件的分组：

SELECT subject, AVG(score) FROM student_score WHERE subject = '计算机是怎样运行的' GROUP BY subject;
SELECT subject, AVG(score) FROM student_socre GROUP BY subject HAVING AVG(score) > 73;
SELECT subject, AVG(score) FROM student_socre GROUP BY subject HAVING MAX(score) > 98;


带排序的分组：

SELECT subject, AVG(score) FROM student_score GROUP BY subject ORDER BY AVG(score) DESC;
SELECT subject, AVG(score) AS avg_score FROM student_score GROUP BY subject ORDER BY avg_score DESC;


带嵌套的分组：

SELECT department, major, COUNT(*) FROM student_info GROUP BY department, major;

简单查询语句顺序汇总

SELECT [DISTINCT] 查询列表
[FROM 表名]
[WHERE 表达式]
[GROUP BY 分组列表]
[HAVING 分组过滤条件]
[ORDER BY 排序列表]
[LIMIT 偏移量, 限制条数]










  • 无标签