--透视和逆透视
select empid, A, B, C, D
from(
select empid ,custid,qty from seven_sale_orders
) as E
[un]pivot(sum(qty) for cusid in(A,B,C,D)) as P;
原表查询数据:
透视后的数据:
--子定义串联的数据
select * from seven_sale_orders
cross join (values('A'),('B'),('C'),('D')) as custs(custid);
select * from seven_sale_orders
cross join (select 'A' as custid
union all select 'B'
union all select 'C'
union all select 'D') as custs;
/*
分组集:
group by:结合合计,根据一列或多列进行分组。
grouping:指示是否聚合group by列表中的指定列表表达式。如grouping返回1则聚合;返回0则不聚合。
rollup/cube:生成简单的group by集合行及小计行或超聚行,还生成一个总计行。rollup选取的列从左到右的进行聚行,cube则从左到右,其生成的小计和超计在总计行之后。
grouping_id 获取当列的汇总的列数(当前汇总在哪列吧,不好说直接上图)
*/
select empid,custid,orderdate,sum(qty) as sumqty from seven_sale_orders
group by cube(empid,custid,orderdate);
--分组集合
select empid,custid,sum(qty) as sumqty from seven_sale_orders
group by
grouping sets
(
(empid ,custid),(empid)--根据此进行分组计算汇总
)