使用group by对数据进行汇总计算

实战背景:

现在有三张表:

  • 商品表。
  • 商品入库表。
  • 商品出库表。

商品表当中只包含基本的商品信息,我现在需要知道我的商品表中的每一项一共出库了多少商品,入库了多少商品。

最终要求展示如下:
在这里插入图片描述
所以我们就需要将三张表进行left join拼接,而后使用group by进行分组获取入库出库表quantity的统计。

错误的解决方法:

select
	id,
	code,
	name,
	unit_id,
	case when sum(put_quantity) is null then 0 else sum(put_quantity) end as total_put_quantity,
	case when sum(out_quantity) is null then 0 else sum(out_quantity) end as total_out_quantity,
	case when sum(put_quantity) is null then 0 else sum(put_quantity) end - 
	case when sum(out_quantity) is null then 0 else sum(out_quantity) end as current_inventory
from (
	select
		a.id,
		a.code,
		a.name,
		a.unit_id,
		b.quantity as put_quantity,
		c.quantity as out_quantity
	from product as a
	left join put_in_storage_item as b on a.id=b.product_id
	left join out_storage_item as c on a.id=c.product_id
) as d
group by id, code, name, unit_id;

在这里插入图片描述

笔者到这里整个人是懵的,为什么会是三十,应该是五才对啊!!!
PS:笔者只做了一个出库的操作。并且明确是五个,但是这里是三十,所以这个SQL是有问题的。

找问题:
我们可以先缩减sql到只查left join三个表那里,看看那里返回来的表是什么样的
在这里插入图片描述

原因就是我们不应该直接把三个表连接,这样会导致有重复的数据。

正确的解决方法:

先分别使用商品表连接另外俩个关联表,最后将他们俩个的结果在连接起来即可~

select n.id,
     n.code,
     n.name,
     n.total_put_quantity,
     m.total_out_quantity,
     n.total_put_quantity - m.total_out_quantity as current_inventory
from (
    select
     id,
     code,
     name,
     case when sum(put_quantity) is null then 0 else sum(put_quantity) end as total_put_quantity
    from (
     select
      a.id,
      a.code,
      a.name,
      b.quantity as put_quantity
     from product as a
     left join put_in_storage_item as b on a.id=b.product_id
    ) as d
    group by id, code, name
) as n 
left join (
    select
     id,
     code,
     name,
     case when sum(out_quantity) is null then 0 else sum(out_quantity) end as total_out_quantity
    from (
     select
      a.id,
      a.code,
      a.name,
      c.quantity as out_quantity
     from product as a
     left join out_storage_item as c on a.id=c.product_id
    ) as d
    group by id, code, name
) as m 
on n.id=m.id;

在这里插入图片描述

吴迪软件开发 小程序 ECMAScript 6 React.js
金牛区吴迪软件开发工作室博客
已标记关键词 清除标记