NMONTH NQTY NCOST LMONTH LQTY LCOST
与
MONTH QTY COST
是什么关系?
与
MONTH QTY COST
是什么关系?
解决方案 »
- 数据库备份错误!求解决方法??
- 数据库迁移问题
- Oracle里某个用户Session执行锁表操作成功后,处理一半异常后(比如User挂了),表如何放开
- 求帮助脱离苦海,装了两天ORACLE,没装成功
- 如何查看当前的用户模式?-------------
- 两台电信ADSL的用户是否能够进行ORACLE服务的互访
- ORA-01002: fetch out of sequence问题
- sqlload的问题:如何让oracle正确导入带有千分位的数字呢?
- 如何在分区表上建立位图索引?(分区索引)在线等待~!高分相送!
- 那里有developer2000的下载????
- 十万火急,有一怪SQL脚本,不知道如何执行?
- 如何把下面的sql语句改写成oracle识别的语句?
本月月份 本月数量 本月金额LMONTH LQTY LCOST
上月月份 上月数量 上月金额都是从一个表里提取出来的。
(select ID,month as NMONTH,qty as NQTY,COST as COST,lag(MONTH,3,NULL)over(order by month,id) as LMONTH,lag(QTY,3,0)over(order by month,id) as LQTY,lag(COST,3,0)over(order by month,id) as LCOST
FROM tablename)
where LMONTH is not NULL
lag(month) over(partition by id order by month) lmonth,
lag(month) over(partition by id order by month) lmonth,
lag(qty) over(partition by id order by month) lmonth,
lag(cost) over(partition by id order by month) lmonth
from a
ORDER BY MONTH
select id,month nmonth,qty nqty,cost ncost,
lag(month) over(partition by id order by month) lmonth,
lag(qty) over(partition by id order by month) lqty,
lag(cost) over(partition by id order by month) lcost
from a
ORDER BY MONTH
lag(month) over(partition by id order by to_number(substr(MONTH,6))) lmonth,
lag(qty) over(partition by id order by to_number(substr(MONTH,6))) lqty,
lag(cost) over(partition by id order by to_number(substr(MONTH,6))) lcost
from a
ORDER BY to_number(substr(MONTH,6))
按照你的方法已经解决了该问题。
还有另外一点的要求能不能一起帮我解决了,或者我开一个新贴,你来看看
还有另外两个表b和c
分别存放某ID每个月的事物处理情况,如:
b表
ID MONTH RECQTY LINECOST
0002 2005-9 1 12
0003 2005-9 1 203.00
0001 2005-10 1 1500.00
0002 2005-10 2 12.00
0003 2005-10 3 202.00
0001 2005-11 2 1500.00
0002 2005-11 1 11.00
c表
ID MONTH USEQTY LINECOST
0001 2005-9 3 1581.2
0003 2005-9 5 203.00
0001 2005-10 3 1500.00
0003 2005-10 3 202.00
0001 2005-11 2 1500.00
0002 2005-11 1 11.00
0003 2005-11 4 201.00结合上面的a表
ID MONTH QTY COST
0001 2005-9 8 100.00
0002 2005-9 0 10.00
0003 2005-9 3 203.00
0001 2005-10 5 110.00
0002 2005-10 4 12.00
0003 2005-10 5 202.00
0001 2005-11 2 111.00
0002 2005-11 2 11.00
0003 2005-11 2 201.00
得到以下的结果:
ID NMONTH NQTY NCOST LMONTH LQTY LCOST USEQTY LINECOST1 RECQTY LINECOST2
0001 2005-9 8 100.00 3 1581.2 0 0
0002 2005-9 0 10.00 0 0 1 12
0003 2005-9 3 203.00 5 203.00 1 203
0001 2005-10 5 110.00 2005-9 8 100.00 3 1500 1 1500
0002 2005-10 4 12.00 2005-9 0 10.00 0 0 2 12
0003 2005-10 5 202.00 2005-9 3 203.00 3 202 3 202
0001 2005-11 2 111.00 2005-10 2 110.00 2 1500 2 1500
0002 2005-11 2 11.00 2005-10 2 12.00 1 11 1 11
0003 2005-11 2 201.00 2005-10 2 202.00 4 201 0 0