select 期初数量,期末数量,出入数量,OPERAT_DATE
from wms_u_merge_pallet_record where to_char(operat_date,'yyyy-mm-dd') Between '2010-11-25' and '2010-11-25'
24号入22 出5 17
25号入10 出13 14
要根据是出库和入库做判断
如果是出就减
如果是入就加期初数量==应该是查询时间前一天的
我24号期初数量 结果是17期末数量==当天单据出入的结果
应该是25号 结果是14 请高手帮忙求解sql
from wms_u_merge_pallet_record where to_char(operat_date,'yyyy-mm-dd') Between '2010-11-25' and '2010-11-25'
24号入22 出5 17
25号入10 出13 14
要根据是出库和入库做判断
如果是出就减
如果是入就加期初数量==应该是查询时间前一天的
我24号期初数量 结果是17期末数量==当天单据出入的结果
应该是25号 结果是14 请高手帮忙求解sql
with wms_u_merge_pallet_record as
(
select date '2010-11-24' curtime,'in' type, 22 amount from dual union all
select date '2010-11-24' curtime,'out' type, 5 amount from dual union all
select date '2010-11-25' curtime,'in' type, 10 amount from dual union all
select date '2010-11-25' curtime,'out' type, 13 amount from dual
)
select curtime oper_time,
nvl(lag(totalAmt) over(order by 1), 0) 期初数量,
totalAmt + nvl(lag(totalAmt) over(order by 1), 0) 期末数量,
totalAmt 出入数量
from (select curtime, sum(decode(type, 'in', amount, -amount)) totalAmt
from wms_u_merge_pallet_record
group by curtime)
----模拟了下,希望对你有帮助
SQL>
SQL> with wms_u_merge_pallet_record as
2 (
3 select date '2010-11-24' curtime,'in' type, 22 amount from dual union all
4 select date '2010-11-24' curtime,'out' type, 5 amount from dual union all
5 select date '2010-11-25' curtime,'in' type, 10 amount from dual union all
6 select date '2010-11-25' curtime,'out' type, 13 amount from dual
7 )
8 select curtime oper_time,
9 nvl(lag(totalAmt) over(order by 1), 0) 期初数量,
10 totalAmt + nvl(lag(totalAmt) over(order by 1), 0) 期末数量,
11 totalAmt 出入数量
12 from (select curtime, sum(decode(type, 'in', amount, -amount)) totalAmt
13 from wms_u_merge_pallet_record
14 group by curtime)
15 /OPER_TIME 期初数量 期末数量 出入数量
----------- ---------- ---------- ----------
2010-11-24 0 17 17
2010-11-25 17 14 -3SQL>
结果13
select sum(qty) from wms_u_merge_pallet_record where operat_type='出库' and to_char(operat_date,'yyyy-mm-dd') Between '2010-11-24' and '2010-11-24'
结果5
select sum(qty) from wms_u_merge_pallet_record where operat_type='入库' and to_char(operat_date,'yyyy-mm-dd') Between '2010-11-25' and '2010-11-25';
结果10
select sum(qty) from wms_u_merge_pallet_record where operat_type='入库' and to_char(operat_date,'yyyy-mm-dd') Between '2010-11-24' and '2010-11-24';
结果22
select lag(sum(decode(operat_type,'入库',qty,'出库',-qty),1) over(order by to_char(operat_date,'yyyy-mm-dd')) 期初
,sum(decode(operat_type,'入库',qty,'出库',-qty) 期末
from tb
group by to_char(operat_date,'yyyy-mm-dd') Between '2010-11-24' and '2010-11-25'
select lag(sum(decode(operat_type,'入库',qty,'出库',-qty),1) over(order by to_char(operat_date,'yyyy-mm-dd')) 期初
,sum(decode(operat_type,'入库',qty,'出库',-qty) 期末
from tb
group by to_char(operat_date,'yyyy-mm-dd')
over (order by to_char(operat_type,'yyyy-mm-dd'))) 期初,
sum(decode(operat_type,'入库',qty,'出库',-qty) 期末
from wms_u_merge_pallet_record group by to_char(operat_date,'yyyy-mm-dd')
错误提示 : 出无效的自变量数
over (order by to_char(operat_date,'yyyy-mm-dd')) 期初,
sum(decode(operat_type,'入库',qty,'出库',-qty) 期末
from wms_u_merge_pallet_record group by to_char(operat_date,'yyyy-mm-dd') Between '2010-11-25' and '2010-11-25'帮忙看下
显示出无效自变量数
sum(decode(operat_type,'入库',qty,'出库',-qty)) 期末
from tb
group by to_char(operat_date,'yyyy-mm-dd')
我现在操作的是pl/sql
select lag(sum(decode(operat_type,'入库',qty,'出库',-qty))
over(order by to_char(operat_date,'yyyy-mm-dd')),1) over(order by to_char(operat_date,'yyyy-mm-dd')) 期初,
sum(decode(operat_type,'入库',qty,'出库',-qty))
over(order by to_char(operat_date,'yyyy-mm-dd')) 期末
from tb
select lag(sum(decode(operat_type,'入库',qty,'出库',-qty))
over(order by to_char(operat_date,'yyyy-mm-dd')),1) over(order by to_char(operat_date,'yyyy-mm-dd')) 期初,
sum(decode(operat_type,'入库',qty,'出库',-qty))
over(order by to_char(operat_date,'yyyy-mm-dd')) 期末
from wms_u_merge_pallet_record提示
windows 函数在此禁用
刚用pl/sql 不好意思哦
这么麻烦你
select lag(rn,1) over(order by dt) 期初,rn 期末
from
(select to_char(operat_date,'yyyy-mm-dd') dt,
sum(decode(operat_type,'入库',qty,'出库',-qty)) over(order by to_char(operat_date,'yyyy-mm-dd')) rn
from wms_u_merge_pallet_record)--把你的这个wms_u_merge_pallet_record exp导出来 发给我看看
很头疼 我不会导出pl/sql
导出命令exp system/密码 file=wms_u_merge_pallet_record.dmp tables=此表的创建者.wms_u_merge_pallet_record
C:\>exp system/sys file=emp.dmp tables=scott.empExport: Release 10.2.0.1.0 - Production on 星期五 11月 26 16:06:58 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集即将导出指定的表通过常规路径...
当前的用户已更改为 SCOTT
. . 正在导出表 EMP导出了 14 行
成功终止导出, 没有出现警告。C:\>