SQL1
with source
as
(
select
option as OptionOrAPEXMTM,
REV,
VOL,
rank() over(order by REV desc) as rk
from
P2P.SYSX_REPORT3
where
status='Active' and REV is not null
order by rev desc
)
select
s.rk,
s.OptionOrAPEXMTM,
s.REV,
s.VOL,
( select sum(rev) from source as sc where sc.rk <= s.rk) as Increase,
(select sum(rev) from source) as total,
(( select sum(rev) from source as sc where sc.rk <= s.rk)/(select sum(rev) from source)) as percent --这里
from
source as s
SQL2:
with source
as
(
select
option as OptionOrAPEXMTM,
REV,
VOL,
rank() over(order by REV desc) as rk
from
P2P.SYSX_REPORT3
where
status='Active' and REV is not null
order by rev desc
)
select
s.rk,
s.OptionOrAPEXMTM,
s.REV,
s.VOL,
( select sum(rev) from source as sc where sc.rk <= s.rk) as Increase,
(select sum(rev) from source) as total,
(( select sum(rev) from source as sc where sc.rk <= s.rk)*1.0000/(select sum(rev) from source)) as percent --这里,*1.0000
from
source as s
我用SQL1去一个view的结果,逻辑跟这个一模一样,跑出来的percent是正常的小树,但是SQL1去跑这个view的时候就是0和1了(我猜的是被强制转化成int了). 请教各位,为什么会成这个样子啊?
收起