group by的rollup、cube、grouping、grouping sets 的用法浅析

group by的rollup、cube、grouping、grouping sets 的用法浅析

sql中group by后带cube子句的功能是对GROUP BY后面的字段所有的组合进行分组求和。

首先有这样一张表t_zby_bgyp，作为演示的基础表：

建表语句：

-- Create table
create table T_ZBY_BGYP
(
  id     NUMBER(16) not null,
  bgypbh VARCHAR2(30),   -- 办公用品编号
  bgypmc VARCHAR2(30),   -- 名称
  wplx   NUMBER(12),    -- 物品类型
  zsl    NUMBER(10),   --总数量
  zksl   NUMBER(10),   -- 在库数量
  syjl   VARCHAR2(200),  -- 使用记录
  wpzt   NUMBER(12)   -- 物品状态
)
tablespace TMYH_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_ZBY_BGYP
  add constraint PK_T_ZBY_BGYP primary key (ID)
  using index 
  tablespace TMYH_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

插入字段语句：

insert into T_ZBY_BGYP (ID, BGYPBH, BGYPMC, WPLX, ZSL, ZKSL, SYJL, WPZT)
values (1, '10000001', '笔记本', 2, 13, 13, '啊实打实大', 1);

insert into T_ZBY_BGYP (ID, BGYPBH, BGYPMC, WPLX, ZSL, ZKSL, SYJL, WPZT)
values (3, '10000003', 'U盘', 2, 45, 45, '收电费多少份', 1);

insert into T_ZBY_BGYP (ID, BGYPBH, BGYPMC, WPLX, ZSL, ZKSL, SYJL, WPZT)
values (4, '10000002', '创可贴', 1, 22, 20, null, 1);

insert into T_ZBY_BGYP (ID, BGYPBH, BGYPMC, WPLX, ZSL, ZKSL, SYJL, WPZT)
values (10, '20170807', 'tttt', 2, 12, 12, null, 1);

insert into T_ZBY_BGYP (ID, BGYPBH, BGYPMC, WPLX, ZSL, ZKSL, SYJL, WPZT)
values (11, '20170809', 'tttt', 2, 12, 12, null, 1);

insert into T_ZBY_BGYP (ID, BGYPBH, BGYPMC, WPLX, ZSL, ZKSL, SYJL, WPZT)
values (6, '20170806', '123', 1, 29, 29, null, 1);

insert into T_ZBY_BGYP (ID, BGYPBH, BGYPMC, WPLX, ZSL, ZKSL, SYJL, WPZT)
values (5, '20170806', '123', 1, 20, 20, null, 1);

insert into T_ZBY_BGYP (ID, BGYPBH, BGYPMC, WPLX, ZSL, ZKSL, SYJL, WPZT)
values (7, '20170806', 'test001', 3, 36, 36, null, 1);

insert into T_ZBY_BGYP (ID, BGYPBH, BGYPMC, WPLX, ZSL, ZKSL, SYJL, WPZT)
values (8, '20170806', 'test002', 3, 39, 39, null, 1);

insert into T_ZBY_BGYP (ID, BGYPBH, BGYPMC, WPLX, ZSL, ZKSL, SYJL, WPZT)
values (9, '20170807', '11111111111111111', 1, 0, 0, null, 1);

如图：

grouping的用法:

GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；
如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。
当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。

select grouping(WPLX),WPLX,count(zsl) from t_zby_bgyp group by rollup (WPLX)

rollup的用法：

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

select grouping(WPLX),WPLX,BGYPBH,count(ZSL) from t_zby_bgyp group by rollup (WPLX,BGYPBH)

cube的用法：

--group by cube会对GROUP BY后面的字段所有的组合进行分组求和。
select grouping(WPLX),WPLX,BGYPBH,count(ZSL) from t_zby_bgyp group by cube (WPLX,BGYPBH)

grouping sets的用法：

--使用GROUPING SETS子句可以只返回小计记录，根据返回字段从右向左统计
select WPLX,BGYPBH,count(ZSL) from t_zby_bgyp group by grouping sets (WPLX,BGYPBH)

小弟整理，希望对各位兄弟有帮助。