- GROUP BY: 分组
SELECT ... FROM... WHERE... GROUP BY... ORDER BY...
-
- GROUP BY的顺序要排在ORDER BY之前
- GROUP BY语句的算法对CPU运行效率有很大影响,一定要尽量优化
- GROUP BY分组的对象一般不是原本的记录行,而是对上述第三部分的五个汇总函数得出的结果,根据指定字段来进行拆分,因为如果要按原本记录行来分组,用order by+distinct就可以实现了——这里要注意,如果要按某个字段进行分组,这个字段必须被包括在select的对象里才行,不然就会得到空结果
- 同样也有像ORDER BY里一样的简化写法
EG:对人员表person中不同性别占比进行统计,语句如下
SELECT Sex, COUNT(*)AS amount FROM person GROUP BY Sex
- ROLLUP:对分组结果进行一个合计
/对于一些低版本的数据库,可能不支持rollup和cube函数,需要修改兼容级别到100
USE [master]
GO
ALTER DATABASE
database_name SET COMPATIBILITY_LEVEL = 100
GO
-
- 单维度小计,对一个字段进行group by rollup
SELECT Sex, COUNT(*)AS amount FROM person GROUP BY SexGROUP BY ROLLUP(Sex)
-
-
- ROLLUP过程相当于以下两步:
-
- 执行不带rollup的select&group by,得到前三行结果
- 执行rollup,对rsex进行小计,得到第四行
- 双维度小计,对两个字段进行group by rollup——联系数据透视表的原理进行理解
-
- 当rollup的参数多于一个时,逻辑过程如下
-
- 执行不带rollup的select&group by,得到的是最详细的双维度分类结果
- 首先按照group by rollup()的第一个参数进行小计,就是下列代码中的部门编码,统计各部门总人数,得到每个dept_num的最后一行
- 然后按照第二个参数性别来进行小计,统计所有性别的总人数,得到最后一行
-
SELECT Sex,deptcode,COUNT(*)AS amount FROM person
GROUP BY ROLLUP(deptcode,Sex)
-
- 多维度:以此类推
- CUBE:如果说rollup是层层递进的逻辑,先汇总第一个参数,然后第二个第三个,知道全部,它的数据是从细到粗不断递进的;那么cube则是一个矩阵形式,对所有参数进行排列组合,对所有可能的组合进行一次汇总小计,最后在进行全表汇总。——因此,cube经常被用来生成交叉报表。
-
上图中,sex是维度1,dept是维度2。- group by cube(1,2)——1*2,1,2,总
- 1*2:第1、2、4、5、7、8行
- 1:第3、6、9行
- 2:第10、11行
- 总:第12行
-
- HAVING:给GROUP BY添加限制条件,只返回符合having条件的group——在GROUP BY之后,ORDER BY之前
-
- 一定要区分好where和having,where的条件限制的是select的对象,而having的条件限制的是已经分好的组,因此,having的条件也通常由汇总函数构成,比如只返回平均值大于100的组,只返回最大值大于50的组等。