date_range 子查询从当前日期返回24个月（如果需要24个月范围以外的时间，请进行调整）。左键将其与数据集连接起来，请参见此演示代码中的注解：

with date_range as 
(--this query generates months range, check it's output
select date_format(add_months(concat(date_format(current_date,'yyyy-MM'),'-01'),-s.i),'yyyyMM') as year_month 
  from ( select posexplode(split(space(24),' ')) as (i,x) ) s --24 months
),

your_data as (--use your table instead of this example
select stack(7,
202001, 500,    
202001, 600,    
201912, 100,    
201910, 200,
201910, 100,     
201909, 400,
201601,5000 -----this date is beyond 24 months, hence it is not in the output
) as (YEAR_MONTH, AMOUNT )
)

select d.year_month, sum(nvl(s.amount,0)) as amount --aggregate
  from date_range d 
       left join your_data s on d.year_month=s.year_month
  group by d.year_month;

结果：

d.year_month    amount
201801  0
201802  0
201803  0
201804  0
201805  0
201806  0
201807  0
201808  0
201809  0
201810  0
201811  0
201812  0
201901  0
201902  0
201903  0
201904  0
201905  0
201906  0
201907  0
201908  0
201909  400
201910  300
201911  0
201912  100
202001  1100

使用表代替\u数据子查询。添加 order by 如有必要。

所以如果我理解正确的话，你想添加所有当前丢失的日期，因为它发生了 amount 这几天是0。
您可以使用：

select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) base_date from
    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
    having base_date between curdate() - interval 24 month and curdate();

这基本上创建了一个1970年到2200年之间的日期列表（筛选出您感兴趣的日期）。
想法是从中选择作为子查询，并与当前表（在日期字段上）连接。
示例：为不存在的数据返回空行
至于日期格式（年-月-yyyymm），您可以运行以下命令：

DATE_FORMAT(your_date,'%Y%m')