MySQL中即时库存明细查询-再改进

CREATE or replace view view_name AS select_clause;
#原来创建的视图,如果查询语句有更改,需要使用到这个方法

UNION all
     SELECT 日期,物料名称,规格型号,批号,round(调拨数量,2) as 调拨数量,验收,调入仓库 from `仓库调拨序时簿`
         WHERE 调出仓库='辅料合格' and 调入仓库='试剂合格';
#因为仓管在工作上难免疏忽,有时会形成物料在仓库内部出现不应该出现的调拨数量,这个数量必须加以注意并调取出来,从而保证结果的准确

CREATE VIEW 订单 AS
     SELECT 日期,单据编号,供应商,物料名称,规格型号,单位,sum(数量) as 数量 from `采购订单序时簿`
         WHERE (`供应商` in (select 供应商 from `欠款` WHERE right(类别,1)='易'))
            AND (`物料名称`,`规格型号`) in (SELECT 物料名称,规格型号 from `易制毒化学品目录`)
     GROUP BY 日期,单据编号,供应商,物料名称,规格型号,单位;
#此视图对采购订单中的数量一列实现聚合操作,完成订单中单据编号的唯一性


CREATE or replace view 仓库调拨 AS
     SELECT 日期, 物料名称,规格型号, 批号,round(调拨数量,2) as 调拨数量,验收,调入仓库 from `仓库调拨序时簿`
         WHERE 调入仓库 not in ('辅料合格','试剂合格')
     UNION all
     SELECT 日期,物料名称,规格型号,批号,round(-调拨数量,2),验收,调入仓库 from `仓库调拨序时簿`
         WHERE 调入仓库 in ('辅料合格','试剂合格')
     UNION all
     SELECT 日期,物料名称,规格型号,批号,round(调拨数量,2) as 调拨数量,验收,调入仓库 from `仓库调拨序时簿`
         WHERE 调出仓库='辅料合格' and 调入仓库='试剂合格';
#此视图对仓库调拨依据数据的现实意义进行分类操作


SELECT distinct 物料名称, 规格型号, 批号, 即时库存, 单位, curdate() as 统计日期
from (SELECT date(a.日期) as 订单日期,a.`单据编号`,a.供应商,a.物料名称,a.规格型号,a.数量,a.单位,
          b.批号,b.实收数量,
          c.`调拨数量`,c.`调入仓库`,c.`日期` as 出库日期,
        ifnull(round(SUM(c.调拨数量)over(PARTITION by a.物料名称,b.批号),2),0)as 调拨总量,
        round((`实收数量`-ifnull(SUM(调拨数量)over(PARTITION by 批号),0)),2) as 即时库存
      FROM 订单 a
           left JOIN `外购入库序时簿` b ON a.`单据编号` = b.`源单单号` and a.`供应商` = b.`供应商` 
                 AND a.`物料名称` = b.`物料名称`
           left JOIN 仓库调拨 c  ON b.批号 = c.批号
      ORDER BY 物料名称,批号) temp
where 即时库存>0;