oracle sql语句搜集
1、时间按半年分组：select sum(approve_num), case when to_number(to_char(rq,'mm')) between 1 and 6 then to_char(rq,'yyyy')||'年上半年' else to_char(rq,'yyyy')||'年下半年' end st, bank3from TM_RPT_APP_APPROVAL_ANALYSIS where trunc(rq) > trunc(date '2010-01-01') and trunc(rq) <= trunc(date'2012-12-30') group by case when to_number(to_char(rq,'mm')) between 1 and 6 then to_char(rq,'yyyy')||'年上半年' else to_char(rq,'yyyy')||'年下半年' end, bank3
2 、计算当月和本月数据，以及占比
with myreport as(select t.score_interval score_interval, sum(case when to_char(t.rq,'YYYYMM')='201207' then t.app_num end) this_month, sum(case when to_char(t.rq,'YYYYMM')='201206' then t.app_num end) last_monthfrom TM_RPT_APP_CROSS_ANALYSIS t where bank1 = '河南省分行' and bank2 = '平顶山分行' and t.rq between date'2012-06-01' and date'2012-07-31' group by score_interval)select t.score_interval, t.this_month, t.last_month, ratio_to_report(this_month) over() this_ration, ratio_to_report(last_month) over() last_rationfrom myreport t order by score_interval ;
效果：
http://dl.iteye.com/upload/attachment/0077/9073/71cc9fc4-917a-3b84-82e1-69b9bcf1456d.png
---
页:
[1]