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, bank3
from 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_month
from 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_ration
from myreport t
order by score_interval ;
　　
　　效果：
　　
　　


 　　---