select a.产品,a.期初库存+sum(b.入库数)over(partition by b.产品,a.月份 order by b.日期) as 库存
from stock a,issue b
where a.产品=b.产品
and a.月份=substr(b.日期,1,7)
and a.月份='2014-08'
from stock a,issue b
where a.产品=b.产品
and a.月份=substr(b.日期,1,7)
and a.月份='2014-08'
解决方案 »
- 急!急!急!!Oracle Enterprise Manager Database Control以SYS用户登陆数据库时出现问题????
- 救命啊,物化视图创建执行了一个小时都没创建完.
- oracle sql select 的问题
- sql中显示查询结果中的第4行至第7行
- 询问CASE WHEN的问题
- oracle的imp和exp的问题
- 求一语句
- 同表分组,sql求助
- LINUX下面$ORACLE_SID的问题?谢谢!!
- 請問 使用dbms_xplan.display_cursor 問題?
- ORACLE如何求几列数的平均值
- C#使用Oracle.ManagedDataAccess.Client如何获得执行后的影响行数?
select B.日期,SUM(A.数量) 库存
from
(select trunc(sysdate,'month') 日期,数量 from stock
where 月份=to_char(sysdate,'yyyy-mm')
and 产品='待查询产品'
union all
select trunc(日期),数量 from issue
where to_char(日期,'yyyy-mm')=to_char(sysdate,'yyyy-mm')
and 产品='待查询产品') A,
(SELECT TRUNC(SYSDATE)-ROWNUM 日期 FROM DUAL
CONNECT BY ROWNUM<TO_NUMBER(TO_CHAR(SYSDATE,'DD'))) B
where A.日期<=B.日期
GROUP BY B.日期
ORDER BY B.日期
需要特别注意某些天没有出入库记录,需要特殊处理生成记录
仅仅是一个思路,参考
select 产品,日期,期初库存+sum(sum(数量))over(partition by 产品 order by 日期) 库存
from issue
where to_char(日期,'yyyy-mm')=to_char(sysdate,'yyyy-mm')
group by 日期,产品
FROM (SELECT TRUNC(SYSDATE) - ROWNUM DAYS
FROM DUAL
CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(SYSDATE, 'DD'))) V1,
(SELECT 'STOCK' AS DATA_TYP,
TO_DATE('1900-01-01', 'yyyy-mm-dd') AS RCV_ISSUE_DATE,
T001.ITEM_CD, --产品
T001.WH_CD, --仓库
T001.INV_STOCK_QTY AS ADD_QTY --期初库存
FROM T_ITEM_STOCK T001
UNION ALL
SELECT 'RCV_ISSUE' AS DATA_TYP,
TRUNC(V01.RCV_ISSUE_DATE) AS RCV_ISSUE_DATE, --入出库日
V01.ITEM_CD,
V01.WH_CD,
SUM(DECODE(V01.RCV_ISSUE_TYP, 1, V01.RCV_ISSUE_QTY, 0)) -
SUM(DECODE(V01.RCV_ISSUE_TYP, 2, V01.RCV_ISSUE_QTY, 0)) AS ADD_QTY --数量
FROM T_RCV_ISSUE V01
WHERE TO_CHAR(V01.RCV_ISSUE_DATE, 'YYYYMM') =
TO_CHAR(SYSDATE, 'YYYYMM')
GROUP BY TRUNC(V01.RCV_ISSUE_DATE), V01.ITEM_CD, V01.WH_CD) V2
WHERE V1.DAYS >= V2.RCV_ISSUE_DATE
GROUP BY V1.DAYS, V2.ITEM_CD, V2.WH_CD
这是我现在的代码,效率太低了,求优化!!!!
select x1.产品,x2.日期
from(select distinct 产品 from stock where 月份='xxx') x1,(select to_char(trunc(sysdate,'MM')+rownum-1,'YYYY-MM') 日期 from dual connect by rownum<=sysdate-trunc(sysdate,'MM')+1)x2)select vir_date.产品,vir_date.日期,a.期初库存+sum(nvl(b.入库数,0))over(partition by vir_date.产品,a.月份 order by vir_date.日期) as 库存
from vir_date,stock a,issue b
where a.产品=b.产品(+)
and a.月份=substr(b.日期(+),1,7)
and a.月份='xxx'
and a.产品=vir_date.产品
and b.日期(+)=vir_date.产品
order by vir_date.产品,vir_date.日期
每天可能会有多次出库入库的操作,这样的话就重复了把大版语句中的issue b 替换成楼主语句中的V2,应该就可以了