GROUP BY 子句—数据的分组
GROUP BY 子句——数据的分组
聚合函数 (比如 SUM) 常常需要添加 GROUP BY 语句。
一、GROUP BY 子句
GROUP BY 子句用于结合聚合函数，根据一个或多个列对结果集进行分组。在 SELECT 语句里，GROUP BY 子句在 WHERE 子句之后，在 ORDER BY 子句之前。
语法：SELECT 列名, 聚合函数(列名) FROM 表名 [ WHERE 条件 ] GROUP BY 列名 [ ORDER BY 列名 ];
前面的例子中我们用 SUM 这个函数来算出所有店的营业总额。如果我们的需求变成是要算出每一间店(store_name)的营业额(Sales)，就可以利用GROUP BY 子句。
例：由 Store_Information 表里求出每一间店的营业总额：
SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name;
结果:store_name SUM(Sales)
------------------------------------------
Los Angeles 1800
San Diego 250
Boston 700
当我们查询不只一个字段，且其中至少一个字段有聚合函数的运用时，我们就需要用到 GROUP BY 子句。这时，我们需要确定 GROUP BY 子句中包含所有其他的字段。
像 ORDER BY 子句一样，GROUP BY 子句里也可以用整数代表字段名称。
用 GROUP BY 子句实现排序操作与用 ORDER BY 子句的区别是：
-> 所有被选中的、非聚合函数的字段必须列在 GROUP BY 子句里；
-> 除非需要所有聚合函数，否则使用 GROUP BY 子句进行排序通常没有必要；
-> GROUP BY 子句用于对相同的数据进行分组，而 ORDER BY 子句基本上只用于让数据形成次序。
二、HAVING 子句
那么，我们如何对函数产生的值设定条件呢？举例来说，我们可能需要知道哪些店的营业额超过1,500 。这时，我们不能使用WHERE 子句，因为WHERE 无法与聚合函数一起使用。那该怎么办呢？很幸运地，SQL 提供一个 HAVING 的指令，而我们就可以用这个指令来达到这个目的。
HAVING 子句在 SELECT 语句里与 GROUP BY 子句联合使用时，用于告诉 GROUP BY 子句在输出里包含哪些分组。HAVING 对 GROUP BY 的作用相当于 WHERE 对于 SELECT 的作用。
语法：SELECT 列名, 聚合函数(列名) FROM 表名 [ WHERE 条件 ] GROUP BY 列名 HAVING 聚合函数(列名) 操作符 值;
注意：如果被 SELECT 的只有函数栏，那就不需要 GROUP BY 子句。
例：在 Store_Information 表里，找出 Sales 大于1,500 的 store_name ，可以用：
SELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_name HAVING SUM(sales) > 1500;
结果:store_name SUM(Sales)
-------------------------------------------
Los Angeles 1800
三、查询举例
有学生表 Student(Sno, Sname, Sage, Ssex)，包括学号、姓名、年龄、性别四个字段；
课程表 Course(Cno, Cname, Tno)，包括课程编号、课程名、教师编号三个字段；
成绩表 SC(Sno, Cno, Score)，包括学号、课程编号、成绩三个字段；
教师表 Teacher(Tno, Tname)，包括教师编号、姓名两个字段；
完成如下查询：
1、查询姓"张"的学生名单
SELECT Sname FROM Student WHERE Sname like '张%'；
2、求选了课程的学生人数
SELECT count(*) FROM SC;
3、查询姓"李"的老师的个数
SELECT count(distinct(Tname))FROM Teacher WHERE Tname like '李%'；
4、查询不及格的课程，并按课程号从大到小排列
SELECT Cno FROM SC WHERE Score <60 ORDER BY Cno;
5、检索"004"课程分数小于60，按分数降序排列的同学学号
SELECT Sno FROM SC WHERE Cno='004' and Score <60 ORDER BY Score desc;
6、查询各个课程及相应的选修人数
SELECT count(*) FROM SC GROUP BY Cno;
7、查询每门课程被选修的学生数
SELECT Cno,count(Sno) FROM SC GROUP BY Cno;
8、统计每门课程的学生选修人数，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT Cno as 课程号，count(*) as 人数 FROM SC GROUP BY Cno ORDER BY count(*) desc, Cno;
9、检索至少选修两门课程的学生学号
SELECT Sno FROM SC GROUP BY Sno HAVING count(*) >=2;
10、查询平均成绩大于60分的同学的学号和平均成绩
SELECT Sno,avg(Score) FROM SC GROUP BY Sno HAVING avg(Score) >60;
11、查询男生、女生人数
SELECT count(Ssex) as 男生人数 FROM Student GROUP BY Ssex HAVING Ssex='男';
SELECT count(Ssex) as 女生人数 FROM Student GROUP BY Ssex HAVING Ssex='女';
12、查询同名同姓学生名单，并统计同名人数
SELECT Sname,count(*) FROM Student GROUP BY Sname HAVING count(*) >1;
13、删除"002"同学的"001"课程的成绩
DELECT FROM SC WHERE Sno='002' AND Cno='001';
14、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT Cno, avg(score) FROM SC GROUP BY Cno ORDER BY avg(score), Cno DESC;
【参考资料】
W3SChool 在线SQL教程 http://www.w3SChool.com.cn/sql/sql_groupby.asp SQL GROUP BY 语句
http://www.w3SChool.com.cn/sql/sql_HAVING.asp SQL HAVING 子句
课后练习：
1. 下面的SQL语句能正常执行吗?
a.
SELECT SUM(SALARY), EMP_ID
FROM EMPLOYEE_PAY_TBL
GROUP BY 1 AND 2;
b.
SELECT EMP_ID, MAX(SALARY)
FROM EMPLOYEE_PAY_TBL
GROUP BY SALARY, EMP_ID;
c.
SELECT EMP_ID , COUNT(SALARY)
FROM EMPLOYEE_PAY_TBL
ORDER BY EMP_ID
GROUP BY SALARY;
d.
SELECT YEAR(DATE_HIRE) AS YEAR_HIRED, SUM(SALARY)
FROM EMPLOYEE_PAY_TBL
GROUP BY 1
HAVING SUM(SALARY) > 20000;
3. 判断正误：
1）在使用 HAVING 子句时一定也要使用 GROUP BY 子句。
2）下面的SQL语句返回分组的薪水总和：
SELECT SUM(SALARY)
FROM EMPLOYEE_PAY_TBL;
3）被选中的字段在 GROUP BY 子句里必须以相同次序出现。
4）HAVING 子句告诉 GROUP BY 子句要包括哪些分组。
4. 运行数据库 learnsql，输入如下查询来显示表 EMPLOYEE_TBL 里的全部城市：
SELECT CITY
FROM EMPLOYEE_TBL;
输入如下查询，把结果与上面的结果进行比较：
SELECT CITY, COUNT(*)
FROM EMPLOYEE_TBL
GROUP BY CITY;
5. HAVING 子句与 WHERE 子句的相似之处在于都可以指定返回数据的条件。WHERE 子句是查询的主过滤器，而 HAVING 子句是在 GROUP BY 子句对数据进行分组之后进行过滤。输入如下查询来了解 HAVING 子句的工作方式：
SELECT CITY,COUNT(*)
FROM EMPLOYEE_TBL
GROUP BY CITY
HAVING COUNT(*) > 1;
修改上面的查询，把结果按降序排序，也就是数值从大到小。
6. 编写一个查询，从 EMPLOYEE_PAY_RATE 表里列出每个城市的平均税率和工资。
7. 编写一个查询，从 EMPLOYEE_PAY_RATE 表里列出每个城市平均薪水高于$20000的每个城市的平均薪水。
8. 有学生表 Student(Sno, Sname, Sage, Sdept)，包括字段学号、姓名、年龄、系名；
及成绩表 SC(studentid, courseid，grade)，包括字段学生学号、课程编号、成绩。
完成以下查询：
1）查询每个学生的平均成绩。
2）查询学生的平均成绩在70分以上的。
9. 在4.1节的练习1中，我们为数据库创建的四个表----学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）填充了数据。在此基础上完成以下查询：
1）查询Student表中的所有记录的Sname、Ssex和Class列。
2）查询教师所有的单位即不重复的Depart列。
3）查询Student表的所有记录。
4）查询Score表中成绩在60到80之间的所有记录。
5）查询Score表中成绩为85，86或88的记录。
6）查询Student表中"95031"班或性别为"女"的同学记录。
7）以Class降序查询Student表的所有记录。
8）以Cno升序、Degree降序查询Score表的所有记录。
9）查询"95031"班的学生人数。
10）查询'3-105'号课程的平均分。
11）查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
12）查询最低分大于70，最高分小于90的Sno列。
13）查询所有学生的Sname、Cno和Degree列。
14）查询所有学生的Sno、Cname和Degree列。
15）查询所有学生的Sname、Cname和Degree列。
16）查询95033班和95031班全体学生的记录。
17）查询存在有85分以上成绩的课程Cno.
18）查询"计算机系"与"电子工程系"不同职称的教师的Tname和Prof。
19）查询所有教师和同学的name、sex和birthday.
20）查询所有"女"教师和"女"同学的name、sex和birthday.
21）查询至少有2名男生的班号。
22）查询Student表中不姓"王"的同学记录。
23）查询Student表中每个学生的姓名和年龄。
24）查询Student表中最大和最小的Sbirthday日期值。
25）以班号和年龄从大到小的顺序查询Student表中的全部记录。
26）查询"男"教师及其所上的课程。
27）查询最高分同学的Sno、Cno和Degree列。