更新时间:2023-01-29 21:01:24
Oracle has a good array of date manipulation functions. The two pertinent ones for this problem are
MONTHS_BETWEEN()
计算两个日期之间的月数 ADD_MONTHS()
将日期增加给定的月数MONTHS_BETWEEN()
which calculates the number of months between two datesADD_MONTHS()
which increments a date by the given number of months我们可以结合使用这些功能来生成一张表格,其中包含您的表格记录所涵盖的所有月份.然后,我们使用外部联接将有条件的记录从USER_INFO联接到该日历.当没有记录匹配时, count(id)
将为零.
We can combine these functions to generate a table of all the months spanned by your table's records. Then we use an outer join to conditionally join records from USER_INFO to that calendar. When no records match count(id)
will be zero.
with cte as (
select max(trunc(created, 'MM')) as max_dt
, min(trunc(created, 'MM')) as min_dt
from user_info
)
, cal as (
select add_months(min_dt, (level-1)) as mth
from cte
connect by level <= months_between(max_dt, min_dt) + 1
)
select to_char(cal.mth, 'YYYY-MM') as operation
, count(id)
from cal
left outer join user_info
on trunc(user_info.created, 'mm') = cal.mth
group by rollup (cal.mth)
order by 1
/