with
tmp_table
as(select'1001' group1,'a' group2,'Y' group3,1 num from dual
unionallselect'1001' group1,'a' group2,'N' group3,2 num from dual
unionallselect'1001' group1,'a' group2,'Y' group3,3 num from dual
unionallselect'1001' group1,'b' group2,'N' group3,4 num from dual
unionallselect'1001' group1,'b' group2,'Y' group3,5 num from dual
unionallselect'1002' group1,'a' group2,'N' group3,6 num from dual
unionallselect'1002' group1,'a' group2,'Y' group3,7 num from dual
unionallselect'1002' group1,'b' group2,'N' group3,8 num from dual
unionallselect'1002' group1,'b' group2,'Y' group3,9 num from dual
)select group1, group2, group3,sum(num) num
from tmp_table
groupby group1, group2, group3
orderby group1, group2, group3
進階使用
with
tmp_table
as(select'1001' group1,1 num from dual
unionallselect'1001' group1,2 num from dual
unionallselect'1001' group1,3 num from dual
unionallselect'1001' group1,4 num from dual
unionallselect'1001' group1,5 num from dual
unionallselect'1002' group1,6 num from dual
unionallselect'1002' group1,7 num from dual
unionallselect'1002' group1,8 num from dual
unionallselect'1002' group1,9 num from dual
)select group1, num,sum(num)over(partitionby group1 orderby group1) ttl_num
from tmp_table
orderby group1
進階使用 累加
with
tmp_table
as(select'1001' group1,1 num from dual
unionallselect'1001' group1,2 num from dual
unionallselect'1001' group1,3 num from dual
unionallselect'1001' group1,4 num from dual
unionallselect'1001' group1,5 num from dual
unionallselect'1002' group1,6 num from dual
unionallselect'1002' group1,7 num from dual
unionallselect'1002' group1,8 num from dual
unionallselect'1002' group1,9 num from dual
)select group1, num,sum(num)over(partitionby group1 orderby group1 ascrowsbetweenunboundedprecedingandcurrentrow) num
from tmp_table
orderby group1
進階使用 grouping sets 每一層都會sum一下
with
tmp_table
as(select'1001' group1,'a' group2,'Y' group3,1 num from dual
unionallselect'1001' group1,'a' group2,'N' group3,2 num from dual
unionallselect'1001' group1,'a' group2,'Y' group3,3 num from dual
unionallselect'1001' group1,'b' group2,'N' group3,4 num from dual
unionallselect'1001' group1,'b' group2,'Y' group3,5 num from dual
unionallselect'1002' group1,'a' group2,'N' group3,6 num from dual
unionallselect'1002' group1,'a' group2,'Y' group3,7 num from dual
unionallselect'1002' group1,'b' group2,'N' group3,8 num from dual
unionallselect'1002' group1,'b' group2,'Y' group3,9 num from dual
)select group1, group2, group3,sum(num) num
from tmp_table
groupby grouping sets((group1, group2, group3),(group1, group2),(group1))orderby group1, group2, group3
進階使用 rollup 在grouping sets的基礎上,增加一個總的合計
with
tmp_table
as(select'1001' group1,'a' group2,'Y' group3,1 num from dual
unionallselect'1001' group1,'a' group2,'N' group3,2 num from dual
unionallselect'1001' group1,'a' group2,'Y' group3,3 num from dual
unionallselect'1001' group1,'b' group2,'N' group3,4 num from dual
unionallselect'1001' group1,'b' group2,'Y' group3,5 num from dual
unionallselect'1002' group1,'a' group2,'N' group3,6 num from dual
unionallselect'1002' group1,'a' group2,'Y' group3,7 num from dual
unionallselect'1002' group1,'b' group2,'N' group3,8 num from dual
unionallselect'1002' group1,'b' group2,'Y' group3,9 num from dual
)select group1, group2, group3,sum(num) num
from tmp_table
groupby rollup(group1, group2, group3)orderby group1, group2, group3
進階使用 cube 所有層都會有一個合計
with
tmp_table
as(select'1001' group1,'a' group2,'Y' group3,1 num from dual
unionallselect'1001' group1,'a' group2,'N' group3,2 num from dual
unionallselect'1001' group1,'a' group2,'Y' group3,3 num from dual
unionallselect'1001' group1,'b' group2,'N' group3,4 num from dual
unionallselect'1001' group1,'b' group2,'Y' group3,5 num from dual
unionallselect'1002' group1,'a' group2,'N' group3,6 num from dual
unionallselect'1002' group1,'a' group2,'Y' group3,7 num from dual
unionallselect'1002' group1,'b' group2,'N' group3,8 num from dual
unionallselect'1002' group1,'b' group2,'Y' group3,9 num from dual
)select group1, group2, group3,sum(num) num
from tmp_table
groupby cube(group1, group2, group3)orderby group1, group2, group3
grouping
with
tmp_table
as(select'1001' group1,'a' group2,'Y' group3,1 num from dual
unionallselect'1001' group1,'a' group2,'N' group3,2 num from dual
unionallselect'1001' group1,'a' group2,'Y' group3,3 num from dual
unionallselect'1001' group1,'b' group2,'N' group3,4 num from dual
unionallselect'1001' group1,'b' group2,'Y' group3,5 num from dual
unionallselect'1002' group1,'a' group2,'N' group3,6 num from dual
unionallselect'1002' group1,'a' group2,'Y' group3,7 num from dual
unionallselect'1002' group1,'b' group2,'N' group3,8 num from dual
unionallselect'1002' group1,'b' group2,'Y' group3,9 num from dual
)select group1, group2, group3,sum(num) num, grouping(group1), grouping(group2), grouping(group3)from tmp_table
groupby grouping sets((group1, group2, group3),(group1, group2),(group1))orderby group1, group2, group3
普通使用with tmp_tableas( select '1001' group1, 'a' group2, 'Y' group3, 1 num from dual union all select '1001' group1, 'a' group2, 'N' group3, 2 num from dual union all select...