复杂的多表清单 SQL 查询 [英] Complex Multiple Table Inventory SQL Query

问题描述

我有 3 个表:假设2019-07-19"是(当前日期)

I have 3 Tables: Assuming "2019-07-19" is the (Current Date)

1.库存:

___________________________________________________
|ID| TransactionDate | ItemID | ItemName |Quantity|
+-------------------------------------------------+
|1 |2019-07-18       | 1      |Lemon     |100     |
+-------------------------------------------------+
|2 |2019-07-19       | 2      |Sugar     |100     | 
+-------------------------------------------------+
|3 |2019-07-19       | 2      |Sugar     |100     |
+-------------------------------------------------+
|4 |2019-07-18       | 1      |Lemon     |100     |
+-------------------------------------------------+ 
|5 |2019-07-19       | 1      |Lemon     |100     |
+-------------------------------------------------+
|6 |2019-07-18       | 2      |Sugar     |100     |
+-------------------------------------------------+ 
|7 |2019-07-18       | 1      |Lemon     |100     |
+-------------------------------------------------+

2.ConsumedItems:

2.ConsumedItems:

___________________________________________________ 
|ID| TransactionDate | ItemID | ItemName |Quantity|
+-------------------------------------------------+
|1 |2019-07-18       | 1      |Lemon     |10      |
+-------------------------------------------------+
|2 |2019-07-19       | 2      |Sugar     |10      |
+-------------------------------------------------+
|3 |2019-07-19       | 2      |Sugar     |10      |
+-------------------------------------------------+
|4 |2019-07-18       | 1      |Lemon     |10      |
+-------------------------------------------------+
|5 |2019-07-19       | 1      |Lemon     |10      |
+-------------------------------------------------+
|6 |2019-07-18       | 2      |Sugar     |10      |
+-------------------------------------------------+
|7 |2019-07-18       | 1      |Lemon     |10      |
+-------------------------------------------------+

3.损坏的物品:

___________________________________________________  
|ID| TransactionDate | ItemID | ItemName |Quantity|
+-------------------------------------------------+
|1 |2019-07-18       | 1      |Lemon     |10      |
+-------------------------------------------------+
|2 |2019-07-19       | 2      |Sugar     |10      |
+-------------------------------------------------+
|3 |2019-07-19       | 2      |Sugar     |10      |
+-------------------------------------------------+
|4 |2019-07-18       | 1      |Lemon     |10      |
+-------------------------------------------------+  
|5 |2019-07-19       | 1      |Lemon     |10      |
+-------------------------------------------------+
|6 |2019-07-18       | 2      |Sugar     |10      |
+-------------------------------------------------+
|7 |2019-07-18       | 1      |Lemon     |10      |
+-------------------------------------------------+

我怎样才能得到这个输出?

How can I get this Output?

1. PrevBalance=[所有先前库存的总和] - ([所有先前损坏的总和] + [所有消耗的总和])
2. DmgToday=[所有当前损坏的总和]
3. CnsmdToday=[所有当前消耗的总和]
4. DlvrdToday=[今天库存中所有项目的总和]

<小时>

| ItemID | ItemName |PrevBalance|DlvrdToday|DmgToday|CnsmdToday|CurrentBal
+------------------------------------------------------------------------+
|1       |Lemon     | 240       |100       |10      |10        |320      |
+------------------------------------------------------------------------+
|2       |Sugar     | 80        |200       |20      |20        |240      |
+-------------------------------------------------------------------------+

工作代码:

select Inventory.ItemID, Inventory.ItemName,  
sum(case when Inventory.TransactionDate < CURDATE() then Inventory.Quantity 
else 0 end)-sum(case when ConsumedItems.TransactionDate <CURDATE() then 
ConsumedItems.Quantity else 0 end)-sum(case when 
DamagedItems.TransactionDate<CURDATE() then DamagedItems.Quantity else 0 end) as 'PrevBalance',

sum(case when Inventory.TransactionDate=CURDATE() then Inventory.Quantity else 0 end) as 'DeliveredToday',

sum(case when DamagedItems.TransactionDate=CURDATE() then DamagedItems.Quantity else 0 end) as 'DamagedToday',

sum(case when ConsumedItems.TransactionDate=CURDATE() then ConsumedItems.Quantity else 0 end) as 'ConsumedToday',

sum(case when Inventory.TransactionDate < CURDATE() then Inventory.Quantity else 0 end)-
sum(case when ConsumedItems.TransactionDate <CURDATE() then ConsumedItems.Quantity else 0 end)-
sum(case when DamagedItems.TransactionDate<CURDATE() then DamagedItems.Quantity else 0 end)-
sum(case when ConsumedItems.TransactionDate=CURDATE() then ConsumedItems.Quantity else 0 end)-
sum(case when DamagedItems.TransactionDate=CURDATE() then DamagedItems.Quantity else 0 end)+
sum(case when Inventory.TransactionDate=CURDATE() then Inventory.Quantity else 0 end) as 'Total Balance' 

from Inventory 
join ConsumedItems on ConsumedItems.ID = Inventory.ID 
join DamagedItems on DamagedItems.ID = Inventory.ID
group by  Inventory.ItemID, Inventory.ItemName`

推荐答案

使用下面的查询

select Inventory.ItemID, Inventory.ItemName, 
sum(case when Inventory.TransactionDate<currentdate() then Inventory.Quantity else 0 end)+sum(case when ConsumedItems.TransactionDate<currentdate() then ConsumedItems.Quantity else 0 end)-sum(case when DamagedItems.TransactionDate<currentdate() then DamagedItems.Quantity else 0 end) as 'PrevBalance',
sum(case when DamagedItems.TransactionDate=currentdate() then DamagedItems.Quantity else 0 end) as 'DamagedToday',
sum(case when ConsumedItems.TransactionDate=currentdate() then ConsumedItems.Quantity else 0 end) as 'ConsumedToday', 
sum(case when ConsumedItems.TransactionDate=currentdate() then ConsumedItems.Quantity else 0 end)+sum(case when DamagedItems.TransactionDate=currentdate() then DamagedItems.Quantity else 0 end)+sum(case when Inventory.TransactionDate=currentdate() then Inventory.Quantity else 0 end) as 'DeliveredToday' 
from Inventory 
join ConsumedItems on ConsumedItems.ID = Inventory.ID 
join DamagedItems on DamagedItems.ID = Inventory.ID
group by  Inventory.ItemID, Inventory.ItemName

这篇关于复杂的多表清单 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！