数据库基础：数据分组

一、输入数据

      alter table T_Employee ADD FSubCompany varchar(20);          
2 alter table T_Employee add FDepartment varchar(20);          
3 ------------------          
4 update T_Employee set FSubCompany='Beijing',FDepartment='Development'          
5 where FNumber='DEV001';          
6           
7 update T_Employee set FSubCompany='Shenzhen',FDepartment='Development'          
8 where FNumber='DEV002';          
9           
10 update T_Employee set FSubCompany='Beijing',FDepartment='HR'          
11 where FNumber='HR001';          
12           
13 update T_Employee set FSubCompany='Beijing',FDepartment='HR'          
14 where FNumber='HR002';          
15           
16 update T_Employee set FSubCompany='Beijing',FDepartment='IT'          
17 where FNumber='IT001';          
18           
19 update T_Employee set FSubCompany='Shenzhen',FDepartment='IT'          
20 where FNumber='IT002';          
21           
22 update T_Employee set FSubCompany='Beijing',FDepartment='Sales'          
23 where FNumber='SALES001';          
24           
25 update T_Employee set FSubCompany='Beijing',FDepartment='Sales'          
26 where FNumber='SALES002';          
27           
28 update T_Employee set FSubCompany='Shenzhen',FDepartment='Sales'          
29 where FNumber='SALES003';   

二、数据分组入门：GROUP BY

关键字：

GROUP BY；

作用：

1、按条件选取字段并排除重复数值。IOW，按字段分组。

2、分组统计：分组使用聚合函数。

• e.g.分年龄统计平均工资

  

• e.g.先按分公司，再按年龄分组

  

• e.g.先按年龄，再按分公司分组

  

• 也可以用order by指定排列顺序

  

PS:

GROUP BY必须在WHERE语句段之后

三、使用HAVING语句对分组进行过滤

聚合函数不能再WHERE语句中使用，必须使用HAVING子句代替。

PS:order by必须放在最后。估计是DBMS选取数据完成最后才排序，SQL语句也按照这一过程制定语法。