oracle分析函数 Mr

 --网络测试评价考核 
  select (a.rq || '月') 月份, b.分值, B.排名
  from (select to_number(to_char(add_months(sysdate, rownum - 12), 'mm')) rq,
               月份
          from (select rownum 月份 FROM DUAL CONNECT BY ROWNUM <= 12)) a
  left join (select to_char(考核日期, 'mm') 月份, 排名, 分值
               from (select dense_rank() over(PARTITION by 考核日期 order by 分值 desc) as 排名,
                            考核日期,
                            分值,
                            地市
                       from (select t.地市, sum(分值) as 分值, t.考核日期
                               from T_ASSESS_DailyMaintain t
                              where t.考核二级分类 = '网络测试评价指标'
                                and add_months(考核日期, 14) >= sysdate - 5
                              group by t.地市, t.考核日期) x
                      order by x.考核日期) y
              where y.地市 = '沈阳') B
    on a.rq = b.月份
 order by a.月份

--故障数量

SELECT to_char(y.月份, 'mm') || '月' as 月份, y.数量
  from (select 序号, trunc(add_months(sysdate, rownum - 14), 'mm') 月份
          from (select rownum 序号 FROM DUAL CONNECT BY ROWNUM <= 13)) X
  LEFT JOIN (select 月份1 as 月份, count(*) as 数量
               from (select trunc(aradmin.Get_Numtorealdate(t.c700000006),
                                  'mm') as 月份1
                       from aradmin.t357 t
                      where add_months(aradmin.Get_Numtorealdate(t.c700000006),
                                       14) >= sysdate - 5
                        and t.c800020022 = '沈阳') a
              group by a.月份1) Y
    ON X.月份 = Y.月份
 order by x.序号