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

小鱼儿 2023-02-28 12:49 1阅读 0赞

### 文章目录 ###

*   *   *  实战背景：
         *  错误的解决方法：
         *  正确的解决方法：

### 实战背景： ###

现在有三张表：

*  商品表。
 *  商品入库表。
 *  商品出库表。

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

最终要求展示如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzYwNjE1OA_size_16_color_FFFFFF_t_70]  
所以我们就需要将三张表进行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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzYwNjE1OA_size_16_color_FFFFFF_t_70 1]

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

**找问题：**  
我们可以先缩减sql到只查left join三个表那里，看看那里返回来的表是什么样的  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzYwNjE1OA_size_16_color_FFFFFF_t_70 2]

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

### 正确的解决方法： ###

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

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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzYwNjE1OA_size_16_color_FFFFFF_t_70 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzYwNjE1OA_size_16_color_FFFFFF_t_70]: /images/20230209/08cdc7fbe5614b7281e842d9a5bbbe92.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzYwNjE1OA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200721221820265.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzYwNjE1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzYwNjE1OA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200721222337428.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzYwNjE1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzYwNjE1OA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200721221909308.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzYwNjE1OA==,size_16,color_FFFFFF,t_70