sql - 坚持一个有点复杂的 SQL 查询

Question

我目前在我无法开始工作的 SQL 查询项目中陷入困境。

这是我的表格:

[INVENTORY]
====================================================
|  ITEM _ID  |  ITEM_DESC   |   STOCK   |    PPU   |
====================================================
|  1         |  CHAIR WHITE |      200  |    15.00 |
|  2         |  CHAIR BLACK |      150  |    15.00 |
|  3         |  CHAIR GREEN |      100  |    15.00 |
====================================================  

[I_RSV]
==============================================
|  ID  |  TRAN_CODE   |   ITEM_ID  |    QTY  |
==============================================
|   1  |  1001        |      1     |    100  |
|   2  |  1001        |      2     |     50  |
|   3  |  1002        |      1     |     50  |
==============================================

[TRANSACTIONS]
=========================================================
|  TRAN_CODE  |  TRAN_DATE   |   DATE_IN  |   DATE OUT  |
=========================================================
|  1001       |  5/22/2015   |  5/26/2015 |  5/27/2015  |
|  1002       |  5/22/2015   |  5/30/2015 |  5/31/2015  |
=========================================================

所以目标是查询所有项目的详细信息和所有未超过今天日期的保留项目的总和。这将是表格形式的目标:

[TABLE X]
=============================================================
|  ITEM_ID  |  ITEM_DESC   |   PPU  |   STOCK  |  RESERVED  |
=============================================================
|  1        |  CHAIR WHITE |  15.00 |     200  |       150  |
|  2        |  CHAIR BLACK |  15.00 |     150  |        50  |
|  3        |  CHAIR GREEN |  15.00 |     100  |         0  |
=============================================================

到目前为止，这是我所做的:

SELECT ITEM_ID, ITEM_DESC, PPU, STOCK, (SELECT SUM(QTY) FROM I_RSV WHERE 
DATE_OUT < GETDATE() GROUP BY ITEM_ID) FROM INVENTORY

但我似乎无法让它工作。非常感谢任何帮助。

Answer 1

这是我会做的:

1. 将两个查询分开。
2. 将您的选择逻辑放入内联查询并对结果求和。
3. 向左加入两者，这样您仍然可以看到没有任何匹配预订的产品。

您可以在一位查询中执行此操作，但我认为您会发现这样做在可读性和查询构建的简易性方面效果更好。

Create table #INVENTORY (Item_ID int,Item_Desc varchar(50),Stock Int,PPU real);
Insert Into #INVENTORY values
 (1,'CHAIR WHITE',200,15.00)
,(2,'CHAIR BLACK',150,15.00)
,(3,'CHAIR GREEN',100,15.00)

Create Table #I_RSV (ID Int,Tran_Code int,Item_ID int,Qty Int)
Insert into #I_RSV values
(1,1001,1,100),(2,1001,2,50),(3,1002,1,50)

Create Table #TRANSACTIONS
(TRAN_CODE int,TRAN_DATE date, DATE_IN date, [DATE OUT] date)
Insert into #TRANSACTIONS Values
 (1001,'5/20/2015','5/20/2015','5/20/2015')
,(1002,'5/22/2015','5/30/2015','5/31/2015')

Select I.*,Case when R.Qty is null then 0 else R.Qty end as Reserved
from #INVENTORY as I
Left join
    (   select SUM(Qty) as Qty ,R.Item_ID
        from #I_RSV as R inner join #TRANSACTIONS as T 
        on R.Tran_Code=T.TRAN_CODE 
        Where T.[DATE OUT] < GETDATE()
        group by R.Item_ID) as R
    on R.Item_ID=I.Item_ID;

drop table #INVENTORY;
drop table #TRANSACTIONS;
drop table #I_RSV;