- 分组查询:将查询结果按照1个或多个字段进行分组,字段值相同的为一组,关键字:GROUP BY,例:
SELECT gender FROM employee GROUP BY gender;
- 注:使用group by,则查询结果集中的列要么是分组依据列,要么是聚合函数列,在使用分组时,group by后面跟的字段一般都直接放在select后
- 以下表为例:
GROUP BY + GROUP_CONCAT()
- GROUP_CONCAT():将每组的某字段的值显示在同一个字段,例:
#查看该公司有哪些部门,每个部门的成员都有谁
SELECT department,GROUP_CONCAT(name) name FROM employee GROUP BY department;
- 结果如下:
GROUP BY + 聚合函数
- GROUP BY + COUNT(),例:
#查看该公司有哪些部门,每个部门分别有多少人
SELECT department,COUNT(name) FROM employee GROUP BY department;
- 结果为:
- GROUP BY + MAX(),GROUP BY + MIN(),例:
#查看该公司有哪些部门,每个部门的最高工资和最低工资是多少
SELECT department,MAX(salary) max,MIN(salary) min FROM employee GROUP BY department;
- 结果为:
- GROUP BY + SUM(),GROUP BY + AVG(),例:
#查看该公司有哪些部门,每个部门的总工资和平均工资是多少
SELECT department,SUM(salary) sum,AVG(salary) avg FROM employee GROUP BY department;
- 结果为:
GROUP BY + HAVING
- HAVING:用来进一步过滤分组查询后的结果,例:
#查询总工资大于8000的部门名称及其总工资、平均工资
SELECT department,SUM(salary) sum,AVG(salary) avg FROM employee GROUP BY department HAVING SUM(salary)>8000;
- 结果为:
- HAVING与WHERE的区别:
- 1.HAVING只能与GROUP BY一起使用,而WHERE可以不与GROUP BY一起使用;
- 2.HAVING是在分组后对数据进行过滤,如果某行记录没有满足HAVING子句的条件,那么这行记录不会被打印出来;而WHERE是在分组前对数据进行过滤,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组;
- 3.HAVING后面可以使用聚合函数,而WHERE后面不能使用聚合函数;
SQL语句执行顺序
- 书写顺序:SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> LIMIT
- 执行顺序:FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY --> LIMIT
- 例:查询除财务部之外,工资总和不小于6000的部门名称、职员名单、平均工资、总工资,并按总工资降序排列
SELECT department,GROUP_CONCAT(name) name,AVG(salary) avg,SUM(salary) sum
FROM employee
WHERE department != '财务部'
GROUP BY department
HAVING sum >= 6000
ORDER BY sum DESC;
- 结果为: