解决方案 »
- 数据库建模
- 现在的即时通讯软件一般用什么方式获取和更新好友信息的(急。。。)
- oracle 函数问题
- &&&&&求助啊..SQL SERVER中的一个带游标和递归的存储过程.j要改成能在ORACLE中能运行的
- select出重复的数据,有没办法只保留最近的一条记录
- 请问将指定的日期减去一个月的sql如何写? oracle数据库
- 检索的问题,在线等,紧急
- 关于在oracle中链接sqlserver执行查询的问题,如何取得sql server中的image或是text类型的字段?
- 求助:用Pro*C 在 VC 中访问Oracle编译错误的问题
- 如何查数据库中的一个过程的原来的定义??
- PL/SQL 批量插入2000万条数据
- 急求助,oracle批量处理数据问题,谢谢
c_count,
max(c_count) over (order by c_month rows between 1 preceding and 1 preceding ),--last month
max(c_count) over (order by c_month rows between 12 preceding and 12 preceding )--去年同期
from ()--使用外连接,保证每月比有数。--有了这些,至于想再怎么算都行了。
select * from a
model
return updated rows
dimension by (yr,area_class)
--one2 市辖 one3县域 依次类推 其他的几个都是同样的
measures (one1,0 one2,0 one3,two1,0 two2,0 two3,three1,0 three2,0 three3,qsh1,0 qsh2,0 qsh3,jm1,0 jm2,0 jm3,grid1,0 grid2,0 grid3)
(
--一产
one2[any,'3']=round(decode(nvl(one1[cv(yr),'1'],0)+nvl(one1[cv(yr),'2'],0),0,0,one1[cv(yr),'3']*(nvl(one1[cv(yr),'1'],0))/(nvl(one1[cv(yr),'1'],0)+nvl(one1[cv(yr),'2'],0))),2),
one3[any,'3']=round(decode(nvl(one1[cv(yr),'1'],0)+nvl(one1[cv(yr),'2'],0),0,0,one1[cv(yr),'3']*(nvl(one1[cv(yr),'2'],0))/(nvl(one1[cv(yr),'1'],0)+nvl(one1[cv(yr),'2'],0))),2),
--二产 市辖和县域的实际数据
two2[any,'3']=round(decode(nvl(two1[cv(yr),'1'],0)+nvl(two1[cv(yr),'2'],0),0,0,two1[cv(yr),'3']*(nvl(two1[cv(yr),'1'],0))/(nvl(two1[cv(yr),'1'],0)+nvl(two1[cv(yr),'2'],0))),2),
two3[any,'3']=round(decode(nvl(two1[cv(yr),'1'],0)+nvl(two1[cv(yr),'2'],0),0,0,two1[cv(yr),'3']*(nvl(two1[cv(yr),'2'],0))/(nvl(two1[cv(yr),'1'],0)+nvl(two1[cv(yr),'2'],0))),2),
---三产的市辖县域数据
three2[any,'3']=round(decode(nvl(three1[cv(yr),'1'],0)+nvl(three1[cv(yr),'2'],0),0,0,three1[cv(yr),'3']*(nvl(three1[cv(yr),'1'],0))/(nvl(three1[cv(yr),'1'],0)+nvl(three1[cv(yr),'2'],0))),2),
three3[any,'3']=round(decode(nvl(three1[cv(yr),'1'],0)+nvl(three1[cv(yr),'2'],0),0,0,three1[cv(yr),'3']*(nvl(three1[cv(yr),'2'],0))/(nvl(three1[cv(yr),'1'],0)+nvl(three1[cv(yr),'2'],0))),2),qsh2[any,'3']=round(decode(nvl(qsh1[cv(yr),'1'],0)+nvl(qsh1[cv(yr),'2'],0),0,0,qsh1[cv(yr),'3']*(nvl(qsh1[cv(yr),'1'],0))/(nvl(qsh1[cv(yr),'1'],0)+nvl(qsh1[cv(yr),'2'],0))),2),
qsh3[any,'3']=round(decode(nvl(qsh1[cv(yr),'1'],0)+nvl(qsh1[cv(yr),'2'],0),0,0,qsh1[cv(yr),'3']*(nvl(qsh1[cv(yr),'2'],0))/(nvl(qsh1[cv(yr),'1'],0)+nvl(qsh1[cv(yr),'2'],0))),2),jm2[any,'3']=round(decode(nvl(jm1[cv(yr),'1'],0)+nvl(jm1[cv(yr),'2'],0),0,0,jm1[cv(yr),'3']*(nvl(jm1[cv(yr),'1'],0))/(nvl(jm1[cv(yr),'1'],0)+nvl(jm1[cv(yr),'2'],0))),2),
jm3[any,'3']=round(decode(nvl(jm1[cv(yr),'1'],0)+nvl(jm1[cv(yr),'2'],0),0,0,jm1[cv(yr),'3']*(nvl(jm1[cv(yr),'2'],0))/(nvl(jm1[cv(yr),'1'],0)+nvl(jm1[cv(yr),'2'],0))),2),grid2[any,'3']=round(decode(nvl(grid1[cv(yr),'1'],0)+nvl(grid1[cv(yr),'2'],0),0,0,grid1[cv(yr),'3']*(nvl(grid1[cv(yr),'1'],0))/(nvl(grid1[cv(yr),'1'],0)+nvl(grid1[cv(yr),'2'],0))),2),
grid3[any,'3']=round(decode(nvl(grid1[cv(yr),'1'],0)+nvl(grid1[cv(yr),'2'],0),0,0,grid1[cv(yr),'3']*(nvl(grid1[cv(yr),'2'],0))/(nvl(grid1[cv(yr),'1'],0)+nvl(grid1[cv(yr),'2'],0))),2)
);
如果为同比 将cv(yr)替换成cv(yr)-1 就可以了。 其中 b表 可以使用with b as ()子句来进行过滤数据。避免数据量大了。
select sysdate,
to_char(sysdate, 'YYYY-MM') as 当月,
to_char(add_months(sysdate, -1), 'YYYY-MM') as 上月,
to_char(add_months(sysdate, -12), 'YYYY-MM') as 上年
from dual; /*
结果
SYSDATE 当月 上月 上年
----------- ------- ------- -------
2013/12/9 1 2013-12 2013-11 2012-12
连接查询也好嵌套查询也好,实现.
*/