这样?
with d_temp_data as
(
select 1 id,'飞马股' iname,2013 year,'01' mon,300 money from dual union all
select 2 id,'飞马股' iname,2013 year,'02' mon,270 money from dual union all
select 3 id,'飞马股' iname,2013 year,'03' mon,350 money from dual union all
select 4 id,'飞马股' iname,2013 year,'04' mon,180 money from dual union all
select 5 id,'飞马股' iname,2013 year,'05' mon,500 money from dual union all
select 6 id,'飞马股' iname,2013 year,'06' mon,400 money from dual union all
select 7 id,'飞马股' iname,2014 year,'01' mon,210 money from dual union all
select 8 id,'飞马股' iname,2014 year,'02' mon,240 money from dual union all
select 9 id,'飞马股' iname,2014 year,'03' mon,320 money from dual union all
select 10 id,'飞马股' iname,2014 year,'04' mon,480 money from dual union all
select 11 id,'飞马股' iname,2014 year,'05' mon,400 money from dual
)select a.*,
nvl(round(money/lag(money) over(order by id)*100,2),'0')||'%' "同比",
nvl(round(money/lag(money) over(partition by mon order by id)*100,2),'0')||'%' "环比"
from d_temp_data a id iname year mon money 同比 环比
----------------------------------------------------------------------
1 1 飞马股 2013 01 300 0% 0%
2 2 飞马股 2013 02 270 90% 0%
3 3 飞马股 2013 03 350 129.63% 0%
4 4 飞马股 2013 04 180 51.43% 0%
5 5 飞马股 2013 05 500 277.78% 0%
6 6 飞马股 2013 06 400 80% 0%
7 7 飞马股 2014 01 210 52.5% 70%
8 8 飞马股 2014 02 240 114.29% 88.89%
9 9 飞马股 2014 03 320 133.33% 91.43%
10 10 飞马股 2014 04 480 150% 266.67%
11 11 飞马股 2014 05 400 83.33% 80%
with d_temp_data as
(
select 1 id,'飞马股' iname,2013 year,'01' mon,300 money from dual union all
select 2 id,'飞马股' iname,2013 year,'02' mon,270 money from dual union all
select 3 id,'飞马股' iname,2013 year,'03' mon,350 money from dual union all
select 4 id,'飞马股' iname,2013 year,'04' mon,180 money from dual union all
select 5 id,'飞马股' iname,2013 year,'05' mon,500 money from dual union all
select 6 id,'飞马股' iname,2013 year,'06' mon,400 money from dual union all
select 7 id,'飞马股' iname,2014 year,'01' mon,210 money from dual union all
select 8 id,'飞马股' iname,2014 year,'02' mon,240 money from dual union all
select 9 id,'飞马股' iname,2014 year,'03' mon,320 money from dual union all
select 10 id,'飞马股' iname,2014 year,'04' mon,480 money from dual union all
select 11 id,'飞马股' iname,2014 year,'05' mon,400 money from dual
)select a.*,
nvl(round(money/lag(money) over(order by id)*100,2),'0')||'%' "同比",
nvl(round(money/lag(money) over(partition by mon order by id)*100,2),'0')||'%' "环比"
from d_temp_data a id iname year mon money 同比 环比
----------------------------------------------------------------------
1 1 飞马股 2013 01 300 0% 0%
2 2 飞马股 2013 02 270 90% 0%
3 3 飞马股 2013 03 350 129.63% 0%
4 4 飞马股 2013 04 180 51.43% 0%
5 5 飞马股 2013 05 500 277.78% 0%
6 6 飞马股 2013 06 400 80% 0%
7 7 飞马股 2014 01 210 52.5% 70%
8 8 飞马股 2014 02 240 114.29% 88.89%
9 9 飞马股 2014 03 320 133.33% 91.43%
10 10 飞马股 2014 04 480 150% 266.67%
11 11 飞马股 2014 05 400 83.33% 80%
解决方案 »
- 将mysql中表course(cno,cname)集成数据到oracle,在执行接口时出现错误:缺少参数C1_CNO
- 新手,请问ORACLE一般用那种数据类型作为主键
- 求种方法,怎么弄出我想要的
- 请问:如何增加oracle的最大容量限制
- 请教关于SEQUENCE,如何实现删除一条纪录后,其序列号也删除,新增记录补删除记录的编号?
- OracleOraHome92TNSListener找不到,求助!!!
- || 是什么运算符?
- Oracle异常处理哪些方法?
- 一个很easy的问题?sys
- C#连接oracle数据库报错:ORA-12514 TNS 监听程序当前无法识别连接描述符中请求服务
- 如何优化SQL语句速度?
- 帮我看一个SQL,为什么能这么写?
那就这样了..
select a.*,
nvl(round(money/lag(money) over(order by id)*100,2)-100,'0')||'%' "同比",
nvl(round(money/lag(money) over(partition by mon order by id)*100,2)-100,'0')||'%' "环比"
from d_temp_data a
我查出来的 是反的 同比和环比 换下就可以了 同比 第一年的 月份 肯定是没的比 那都是0了 下一年开始 才回有对比
select a.*,
nvl(round(money/lag(money) over(order by id)*100,2)-100,'0')||'%' "环比",
nvl(round(money/lag(money) over(partition by mon order by id)*100,2)-100,'0')||'%' "同比"
from d_temp_data a
我查出来的 是反的 同比和环比 换下就可以了 同比 第一年的 月份 肯定是没的比 那都是0了 下一年开始 才回有对比
select a.*,
nvl(round(money/lag(money) over(order by id)*100,2)-100,'0')||'%' "环比",
nvl(round(money/lag(money) over(partition by mon order by id)*100,2)-100,'0')||'%' "同比"
from d_temp_data a
我试了下 环比对上了,同比怎么都没有结果出现呢?2013年的数据有了,那2014年的同比是有结果。
select a.*,
nvl(round(money/lag(money) over(order by id)*100,2)-100,'0')||'%' "同比",
nvl(round(money/lag(money) over(partition by month order by year)*100,2)-100,'0')||'%' "环比"
from d_temp_data a
nvl(round(avg(t.p004006)/lag(avg(t.p004006)) over(order by substr( t.datacode,2,4))*100,2)-100,'0') VAL3,
nvl(round(sum(t.p001001)/lag(sum(t.p001001)) over(order by substr( t.datacode,2,4))*100,2)-100,'0') VAL2,
nvl(round(sum(t.p042)/lag(sum(t.p042)) over(order by substr( t.datacode,2,4))*100,2)-100,'0') VAL1
from v_city t group by substr( t.datacode,2,4) order by to_number(substr( t.datacode,2,4) )
隐藏第一条数据 求大神帮忙