Oracle中over()

標籤：

select to_date(:fr_date,‘yyyy-mm-dd‘) f_date,to_date(:to_date,‘yyyy-mm-dd‘) t_date,nnd,count(distinct memb_id) memb_count,count(bill_id) bill_count,sum(bill_money) pay_count,to_char(round(sum(bill_money)*100/SUM(sum(bill_money)) OVER( ),2),‘990.99‘)||‘%‘ percent from (
select case when c.year_old>=1 and c.year_old<=10 then ‘1-10‘
when c.year_old>=11 and c.year_old<=20 then ‘11-20‘
when c.year_old>=21 and c.year_old<=30 then ‘21-30‘
when c.year_old>=31 and c.year_old<=40 then ‘31-40‘
when c.year_old>=41 and c.year_old<=50 then ‘41-50‘
else ‘50歲以上‘end as nnd,d.bill_id,d.memb_id,d.bill_date,d.bill_money,year_old
from
(select memb_id,floor(months_between(sysdate,BIRTH_DATE)/12) as year_old from TB_AX001 a) c,
(select bill_id,memb_id,bill_date,bill_money from tb_ax006 b where b.bill_date between to_date(:fr_date,‘yyyy-mm-dd‘) and to_date(:to_date,‘yyyy-mm-dd‘)+0.99999) d
where c.memb_id(+) = d.memb_id )e
group by nnd
order by nnd

--SUM(sum(bill_money)) OVER( )總金額

Oracle中over()