首页 > 数据库 > SQL Server >

SqlServer中分组函数GROUPBY的扩展:all,cube,roolup与grouping

2017-07-26

SqlServer中分组函数GROUPBY的扩展:all,cube,roolup与grouping。概述:GROUP BY很常见,无须解释。GROUP BY结合all, cube,roolup使用起来可以实现很多功能。在数据库报表、统计分析、仓库处理等OLAP(在线联机分析)场合使用GROUP BY及其扩展函数,再结合窗口函数

概述:

GROUP BY很常见,无须解释。GROUP BY结合all, cube,roolup使用起来可以实现很多功能。在数据库报表、统计分析、仓库处理等OLAP(在线联机分析)场合使用GROUP BY及其扩展函数,再结合窗口函数,必将发挥巨大作用,以精简的程序实现强大的功能,起到事半功倍的效果。

具体说到GROUP BY 扩展功能中的CUBE和ROLLUP区别不太大,只是在基于GROUP BY 子句创建和汇总分组的可能的组合上有一定差别,CUBE将返回的更多的可能组合。如果在GROUP BY子句中有N个列或者是有N个表达式的话,SQLSERVER在使用CUBE的结果集上会返回2的N次幂个可能组合。

事例说明:

1、group by all

Transact-SQL 在 GROUP BY 子句中提供 ALL 关键字。只有在 SELECT 语句还包括 WHERE 子句时,ALL 关键字才有意义。

如果使用 ALL 关键字,则即使某些组不包含符合搜索条件的行,查询结果也将包括 GROUP BY 子句生成的所有组。如果不使用 ALL 关键字,则包含 GROUP BY 子句的 SELECT 语句仅显示包含符合搜索条件的行的组。

举例:找出机械学院中的人数。
SELECT
DEPTName,
sum(len) as Total
FROM HRYGZD
WHERE DEPTName= '机械学院'
GROUP BY ALL DEPTName

Name Total
varchar(36) int
机械学院 1220
生物学院 NULL
物理学院 NULL
化工学院 NULL

2、group by cube

group by cube(字段1,字段2,字段3) 可以写成group by (字段1,字段2,字段3) with cube。通常Group By语句是按照其后所跟的所有字段进行分组,而如果加入了CUBE关键字以后,那么系统将根据所有字段进行分组的基础上,还会通过对所有这些分组字段所有可能存在的组合形成的分组条件进行分组计算。如果Group by(a,b,c) with cube

那么分组计算时可分为如下几种分组:(a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),()。

结合使用grouping函数可以用于列中数据存在NULL时的辨别, grouping(列)如果是数据列,则计算值为0,如果为汇总列计算值为1。

举例:

SELECT
CASE grouping(CODE)
WHEN 1 THEN 'all'
ELSE CODE END AS CODE,
CASE grouping(ID)
WHEN 1 THEN 'all'
ELSE IDEND AS ID,
count(*) AS '合计'
FROM LeaveApply
GROUP BY cube(CODE,ID)

----having 过滤
分组((CODE,humanid),(CODE),(humanid),(),2^2 = 4种分组

CODE ID 合计
varchar(36) varchar(36) int
201605270017 0160135e-b693-4a42-b62b-506dd0d9fda5 1
201605270027 0160135e-b693-4a42-b62b-506dd0d9fda5 1
all 0160135e-b693-4a42-b62b-506dd0d9fda5 2
201609200030 02338f05-1b8c-4cfb-9493-dae4c8434977 1
all 02338f05-1b8c-4cfb-9493-dae4c8434977 1
201607190004 0686f583-bf38-4655-b277-c15b40bc031a 1
201608250041 0686f583-bf38-4655-b277-c15b40bc031a 1
all 0686f583-bf38-4655-b277-c15b40bc031a 2
all all 5
201605270017 all 1
201605270027 all 1
201607190004 all 1
201608250041 all 1
201609200030 all 1

3、group by roolup

group by roolup(字段1,字段2) 或者 group by (字段1,字段2) with roolup

ROLLUP会根据GROUP BY后面的字段从右到左逐步以去掉右边一个字段,逐步向上累计求和。

举例:

SELECT
CASE grouping(ID)
WHEN 1 THEN 'all'
ELSE ID END AS ID,
CASE grouping(CODE)
WHEN 1 THEN 'all'
ELSE CODE END AS CODE,
count(*) AS '合计'
FROM LeaveApply
GROUP BY rollup(ID,CODE)
-----having可以过滤数据
分组:(ID,CODE),(ID),()

CODE ID 合计
varchar(36) varchar(36) int
0160135e-b693-4a42-b62b-506dd0d9fda5 201605270017 1
0160135e-b693-4a42-b62b-506dd0d9fda5 201605270027 1
0160135e-b693-4a42-b62b-506dd0d9fda5 all 2
02338f05-1b8c-4cfb-9493-dae4c8434977 201609200030 1
02338f05-1b8c-4cfb-9493-dae4c8434977 all 1
0686f583-bf38-4655-b277-c15b40bc031a 201607190004 1
0686f583-bf38-4655-b277-c15b40bc031a 201608250041 1
0686f583-bf38-4655-b277-c15b40bc031a all 2
all all 5

相关文章
最新文章
热点推荐