select id,no,price,c_price,code,lead(code,1) over(partition by id order by no)
,case code when lead(code,1) over(partition by id order by no)
then price/lead(price,1) over(partition by id order by no)
else c_price/lead(c_price,1) over(partition by id order by no) end
from test
order by id,no;
,case code when lead(code,1) over(partition by id order by no)
then price/lead(price,1) over(partition by id order by no)
else c_price/lead(c_price,1) over(partition by id order by no) end
from test
order by id,no;
然后两个视图比较
select ...,decode(...) from (
select ...lead() ... ...
)...;
----------------------------------------------
根据你提供的数据,可以看出code相同时,price / price1和c_price / c_price 1的值相同,用price / price1和c_price / c_price 1都一样,而code不相同时,你用c_price / c_price 1,可见直接用c_price / c_price 1就可以了你的decode语句也写错了
decode(price = price1,true,price,c_price)是不对的,你要比较code不是吗,应该是这样:
decode(code ,code1,price,c_price)