sql - oracle如何合并两列

Question

我有一个问题

select
    city,
    month,
    month_number,
    sum(totalcount) as totalcount,
    sum(total_value) total_value
from
(
    select
        city,
        to_char( testdate, 'Mon') month,
        to_number( to_char( testdate, 'mm' ) ) month_number,
        count( totalcount ) totalcount,
        sum( total_value ) total_value
    from 
        testtable
    group by
        city,
        testdate
)
group by
    city,
    month,
    month_number
order by
    1,3

将结果作为图像 1，如何修改可以将结果作为图像 2 的查询？

标签： sqloracle

Answer 1

在 group by 中使用 Oracle ROLLUP 函数来达到预期的结果。

select
city,
month,
month_number,
sum(totalcount) as totalcount,
sum(total_value) total_value
from
(
    select
        city,
        to_char( testdate, 'Mon') month,
        to_number( to_char( testdate, 'mm' ) ) month_number,
        count( totalcount ) totalcount,
        sum( total_value ) total_value
    from 
        testtable
    group by
        city,
        testdate
)
group by
    city,
    ROLLUP (month,month_number)
order by
    1,3