复杂的多表清单 SQL 查询 [英] Complex Multiple Table Inventory SQL Query
本文介绍了复杂的多表清单 SQL 查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有 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?
- PrevBalance=[所有先前库存的总和] - ([所有先前损坏的总和] + [所有消耗的总和])
- DmgToday=[所有当前损坏的总和]
- CnsmdToday=[所有当前消耗的总和]
- 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屋!
查看全文