with a as ( select 9 as month, rownum as DAY, rownum * 10 as price, rownum * 100 as amount from dual connect by rownum < 10 ) , b as ( select 10 as month, rownum as DAY, rownum * 10 - 5 as price, rownum * 100 - 5 as amount from dual connect by rownum < 10 ) select (case when grouping(month) = 1 and grouping(DAY) =1 then '总计' when grouping(day) = 1 then to_char(month) || '月份小计' else to_char(month) end) as MONTH , (case when grouping(DAY) =1 then '-' else to_char(DAY) end) as DAY , sum(price) as price , sum(amount) as amount from ( select * from a union all select * from b ) k -- group by grouping sets ((), (month, DAY)) group by rollup (month,day) order by month, DAY ;10 1 5 95 10 2 15 195 10 3 25 295 10 4 35 395 10 5 45 495 10 6 55 595 10 7 65 695 10 8 75 795 10 9 85 895 10月份小计 - 405 4455 9 1 10 100 9 2 20 200 9 3 30 300 9 4 40 400 9 5 50 500 9 6 60 600 9 7 70 700 9 8 80 800 9 9 90 900 9月份小计 - 450 4500 总计 - 855 8955
那更简单。 select d.*, sum(CL) over (partition by MONTH) as "当前CL小计" from 数据表。
select 9 as month, rownum as DAY, rownum * 10 as price, rownum * 100 as amount
from dual
connect by rownum < 10
)
, b as (
select 10 as month, rownum as DAY, rownum * 10 - 5 as price, rownum * 100 - 5 as amount
from dual
connect by rownum < 10
)
select (case when grouping(month) = 1 and grouping(DAY) =1 then '总计'
when grouping(day) = 1 then to_char(month) || '月份小计' else to_char(month) end) as MONTH
, (case when grouping(DAY) =1 then '-' else to_char(DAY) end) as DAY
, sum(price) as price
, sum(amount) as amount
from (
select * from a
union all
select * from b
) k
-- group by grouping sets ((), (month, DAY))
group by rollup (month,day)
order by month, DAY
;10 1 5 95
10 2 15 195
10 3 25 295
10 4 35 395
10 5 45 495
10 6 55 595
10 7 65 695
10 8 75 795
10 9 85 895
10月份小计 - 405 4455
9 1 10 100
9 2 20 200
9 3 30 300
9 4 40 400
9 5 50 500
9 6 60 600
9 7 70 700
9 8 80 800
9 9 90 900
9月份小计 - 450 4500
总计 - 855 8955
select d.*, sum(CL) over (partition by MONTH) as "当前CL小计"
from 数据表。