test_a 表有4个字段:A,B,C,D 。字段A是指标,B、C、D是维度。
一般用group by的时候是这样的写法:
(1)
select a.b servcode,a.c gwid,a.d ismgacount,sum(a.a) mtcount
from test_a a group by a.b,a.c,a.d order by a.b,a.c,a.d;
如果用了group by,如下:
(2)
select a.b servcode,a.c gwid,a.d ismgacount,sum(a.a) mtcount
from test_a a group by grouping sets((a.b,a.c),a.d) order by a.b,a.c,a.d;
实际上,(2)执行的结果,跟(1)没多大关系,(2)执行的结果,实际上是等于将
下面(3),(4)的结果进行union all (自己在test_a里造点数,执行一下就明白的):
(3)
select a.b servcode,a.c gwid, ' ' ismgacount,sum(a.a) mtcount
from test_a a group by a.b,a.c order by a.b,a.c;
(4)
select ' ' servcode,' ' gwid,a.d ismgacount,sum(a.a) mtcount
from test_a a group by a.d order by a.d;
联系客服