SQL每日一题(20210222)仓库期初

time丶sand

已于 2022-05-19 20:10:30 修改

阅读量268

点赞数

分类专栏：每日一练文章标签： sql 数据库 database

于 2022-05-15 17:30:48 首次发布

本文链接：https://blog.csdn.net/weixin_44681259/article/details/124780022

版权

每日一练专栏收录该内容

119 篇文章 17 订阅

订阅专栏

SQL每日一题(20211110)
SQL每日一题(20220422)
在这里插入图片描述

在这里插入图片描述

select a.WAREHOUSE                                                 as 仓库,
       a.ITEM                                                      as 产品,
       sum(ifnull(b.QTY, 0))                                       as 期初,
       sum(ifnull(c.QTY, 0))                                       as 发出,
       sum(ifnull(d.QTY, 0))                                       as 收入,
       sum(ifnull(b.QTY, 0) + ifnull(d.QTY, 0) - ifnull(c.QTY, 0)) as 结存
from (select WAREHOUSE, ITEM
      from T0222A
      union
      select WAREHOUSE, ITEM
      from T0222B
      union
      select WAREHOUSE, ITEM
      from T0222C) a
         left join T0222A b on a.WAREHOUSE = b.WAREHOUSE and a.ITEM = b.ITEM
         left join T0222B c on a.WAREHOUSE = c.WAREHOUSE and a.ITEM = c.ITEM
         left join T0222C d on a.WAREHOUSE = d.WAREHOUSE and a.ITEM = d.ITEM
GROUP BY a.WAREHOUSE, a.ITEM;

在这里插入图片描述

SELECT COALESCE(A.WAREHOUSE, B.WAREHOUSE, C.WAREHOUSE) AS "仓库",
       COALESCE(A.ITEM, B.ITEM, C.ITEM)                AS "产品",
       NVL(A.QTY, 0)                                           AS "期初",
       NVL(B.QTY, 0)                                           AS "发出",
       NVL(C.QTY, 0)                                           AS "收入",
       NVL(A.QTY, 0) - NVL(B.QTY, 0) + NVL(C.QTY, 0)   AS "结存"
FROM T0222A A
         FULL JOIN T0222B B ON A.WAREHOUSE = B.WAREHOUSE AND A.ITEM = B.ITEM
         FULL JOIN T0222C C ON A.WAREHOUSE = C.WAREHOUSE AND A.ITEM = C.ITEM
ORDER BY "仓库";