MySQL查询语句的不断优化过程

数据管理idata 2021-04-06

180

一

SELECT date(a.日期) as 订单日期,a.`单据编号`,a.供应商,a.物料名称,a.数量,a.单位,
       b.批号,b.实收数量,
       c.`调拨数量`,c.`调入仓库`,c.`日期` as 出库日期
FROM `采购订单序时簿` a
   left JOIN `外购入库序时簿` b ON a.`单据编号` = b.`源单单号` AND a.`供应商` = b.`供应商`
   left JOIN `仓库调拨序时簿` c ON b.`物料名称` = c.`物料名称` and b.`规格型号`=c.`规格型号` AND b.批号 = c.批号
WHERE (a.`供应商` in (select 供应商 from `欠款` WHERE right(类别,1)='易'))
   AND (a.`物料名称`,a.`规格型号`) in (SELECT 物料名称,规格型号 from `易制毒化学品目录`);
 #表之间建立关联使用单据编号与供应商,但实际上并不能反映实际业务中的一一对应关系

对查询结果是否符合实际业务情况的查看,可以发现查询结果中出现了不应该出现的结果.

这可以由在查询字段列表里加上一个字段 c.'物料名称' 来发现.这也是我对异常情况查找原因寻找半天后的一个发现.

其原因在于:在一个订单单据编号下同时有两种物料,一种是我们要查询获取的目标,另一种是我们要忽略的物料;此时,他们又同属于一个供应商,那么后续环节物料的入库与出库里也继续同属于一个单据编号与一个供应商,虽然在where条件子句里过滤了供应商与物料名称,但也只是过滤了订单序时簿并没有参与过滤后续环节的表数据,所以仍然会因为是left join的联接方式被关联提取出来.形成下面的结果:

二

解决方式,在join语句里添加:AND a.`物料名称` = b.`物料名称`,同时一并也在第二个left join语句里也再增加一个条件:and b.`规格型号`=c.`规格型号` .也就是说宁可多写一个条件也要使筛选结果更为准确一些,形成这样的结果:

SELECT date(a.日期) as 订单日期,a.`单据编号`,a.供应商,a.物料名称,a.数量,a.单位,
       b.批号,b.实收数量,
       c.`物料名称`, c.`调拨数量`,c.`调入仓库`,c.`日期` as 出库日期
FROM `采购订单序时簿` a
  left JOIN `外购入库序时簿` b ON a.`单据编号` = b.`源单单号` and a.`供应商` = b.`供应商` AND a.`物料名称` = b.`物料名称`
  left JOIN `仓库调拨序时簿` c ON b.`物料名称` = c.`物料名称` and b.`规格型号`=c.`规格型号` AND b.批号 = c.批号
WHERE (a.`供应商` in (select 供应商 from `欠款` WHERE right(类别,1)='易'))
  AND (a.`物料名称`,a.`规格型号`) in (SELECT 物料名称,规格型号 from `易制毒化学品目录`);

三

然后我们再把c.`物料名称`去掉,它的增加是为了检查查询是否合理,使命完成就可以删掉了, 这样我们不仅要使查询结果减少冗余更为简洁,而且也要尽可能地使查询语句更为简洁明白.

SELECT date(a.日期) as 订单日期,a.`单据编号`,a.供应商,a.物料名称,a.数量,a.单位,
       b.批号,b.实收数量,
       c.`调拨数量`,c.`调入仓库`,c.`日期` as 出库日期
FROM `采购订单序时簿` a
    left JOIN `外购入库序时簿` b ON a.`单据编号` = b.`源单单号` and a.`供应商` = b.`供应商` AND a.`物料名称` = b.`物料名称`
    left JOIN `仓库调拨序时簿` c ON b.`物料名称` = c.`物料名称` and b.`规格型号`=c.`规格型号` AND b.批号 = c.批号
WHERE (a.`供应商` in (select 供应商 from `欠款` WHERE right(类别,1)='易'))
    AND (a.`物料名称`,a.`规格型号`) in (SELECT 物料名称,规格型号 from `易制毒化学品目录`);

四

我们再增加语句:SUM(c.调拨数量)over(PARTITION by b.批号) as 调拨总量.依据物料批号分组汇总出同一个批号的物料出库总量,形成新的一列.

SELECT
  date(a.日期) as 订单日期,a.`单据编号`,a.供应商,a.物料名称,a.数量,a.单位,
  b.批号,b.实收数量,
  c.`调拨数量`,c.`调入仓库`,c.`日期` as 出库日期,
    SUM(c.调拨数量)over(PARTITION by b.批号) as 调拨总量
    
FROM
  `采购订单序时簿` a
left JOIN `外购入库序时簿` b ON a.`单据编号` = b.`源单单号` and a.`供应商` = b.`供应商` AND a.`物料名称` = b.`物料名称`
left JOIN `仓库调拨序时簿` c ON b.`物料名称` = c.`物料名称` and b.`规格型号`=c.`规格型号` AND b.批号 = c.批号
WHERE (a.`供应商` in (select 供应商 from `欠款` WHERE right(类别,1)='易'))
AND (a.`物料名称`,a.`规格型号`) in (SELECT 物料名称,规格型号 from `易制毒化学品目录`);

五

我们再增加一句:round((`实收数量`-SUM(调拨数量)over(PARTITION by 批号)),2) as 即时库存.

SELECT
  date(a.日期) as 订单日期,a.`单据编号`,a.供应商,a.物料名称,a.数量,a.单位,
  b.批号,b.实收数量,
  c.`调拨数量`,c.`调入仓库`,c.`日期` as 出库日期,
    round(SUM(c.调拨数量)over(PARTITION by b.批号),2) as 调拨总量,
    round((`实收数量`-SUM(调拨数量)over(PARTITION by 批号)),2) as 即时库存
FROM
  `采购订单序时簿` a
left JOIN `外购入库序时簿` b ON a.`单据编号` = b.`源单单号` and a.`供应商` = b.`供应商` AND a.`物料名称` = b.`物料名称`
left JOIN `仓库调拨序时簿` c ON b.`物料名称` = c.`物料名称` and b.`规格型号`=c.`规格型号` AND b.批号 = c.批号
WHERE (a.`供应商` in (select 供应商 from `欠款` WHERE right(类别,1)='易'))
AND (a.`物料名称`,a.`规格型号`) in (SELECT 物料名称,规格型号 from `易制毒化学品目录`);

六

还可以增加语句不?可以.

SELECT distinct temp.物料名称, temp.规格型号, temp.批号, temp.即时库存,curdate() as 统计日期
from(
    SELECT date(a.日期) as 订单日期,a.`单据编号`,a.供应商,a.物料名称,a.规格型号,a.数量,a.单位,
     b.批号,b.实收数量,
     c.`调拨数量`,c.`调入仓库`,c.`日期` as 出库日期,
    round(SUM(c.调拨数量)over(PARTITION by b.批号),2) as 调拨总量,
    round((`实收数量`-SUM(调拨数量)over(PARTITION by 批号)),2) as 即时库存
  FROM `采购订单序时簿` a
    left JOIN `外购入库序时簿` b ON a.`单据编号` = b.`源单单号` and a.`供应商` = b.`供应商` AND a.`物料名称` = b.`物料名称`
    left JOIN `仓库调拨序时簿` c ON b.`物料名称` = c.`物料名称` and b.`规格型号`=c.`规格型号` AND b.批号 = c.批号
  WHERE (a.`供应商` in (select 供应商 from `欠款` WHERE right(类别,1)='易'))
     AND (a.`物料名称`,a.`规格型号`) in (SELECT 物料名称,规格型号 from `易制毒化学品目录`)
     ORDER BY 物料名称,批号) temp
where temp.即时库存>0
ORDER BY 即时库存 DESC;

人并不是活一辈子,而是活几个瞬间.

可能有1%的时间片段构成了我们99%的生命,其他的都是重复的填充.

晚安,祝有好梦.

mysql

文章转载自数据管理idata，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL查询语句的不断优化过程

评论