视图显示库存余量

实现效果：

CREATE VIEW  [dbo].[v_Show_StockInOrderItem]

AS
WITH cte_detail AS
(
   --已入库数量
SELECT s.ProductID,s.ColorsID,s.SizesID,s.Title,s.ColorsName,s.SizesName,s.Quantity AS qty 
FROM TheBeerHouse.StockInItems s 
LEFT JOIN TheBeerHouse.StockIn si ON si.StockInID = s.StockInID

WHERE si.Status='Audited'     --已审核的标志，

              --TheBeerHouse.StockIn相当于是一个订单，谁什么时间入库   

              --TheBeerHouse.StockInItems 与订单表相关联，详细这个订单入库的哪个产品，数量，颜色，尺码等

UNION ALL

--已出库数量
SELECT o.ProductID AS oProductID,o.ColorsID AS oColorsID,o.SizesID AS oSizesID,o.Title AS otitle,
o.ColorsName AS oColorsName,o.SizesName AS oSizesName,-o.Quantity AS qty
FROM TheBeerHouse.OrderItems o
LEFT JOIN TheBeerHouse.Orders oo ON oo.OrderID = o.OrderID

WHERE oo.ShippingStatus = 'ApplyShipped'       --已出库的标志

              --TheBeerHouse.Orders 出库订单，谁什么时间出库   

              --TheBeerHouse.OrderItems 与订单表相关联，详细这个订单出库的哪个产品，数量，颜色，尺码等

)

--把上边的结果都结合起来

,cte_qty AS
(
SELECT ProductID,SUM(qty) AS qty,Title,SizesID,SizesName,ColorsID,ColorsName      --qty为入库量-出库量也就是库存余量
FROM cte_detail
WHERE ProductID=ProductID AND ColorsID=ColorsID AND SizesID=SizesID
GROUP BY ProductID,Title,SizesID,SizesName,ColorsID,ColorsName
)
SELECT   ROW_NUMBER()  OVER(ORDER BY T.ProductID) AS '数据标识' 
,产品标识 = T.ProductID
,ROW_NUMBER()  OVER(ORDER BY t.ProductID) AS '序号'
,产品编号 = T.ProductID
,产品名称 = T.Title
,颜色=T.ColorsName
,尺码=T.SizesName
,入库总量=(SELECT ISNULL(SUM(s.Quantity),0)
FROM TheBeerHouse.StockInItems s 
LEFT JOIN TheBeerHouse.StockIn si ON si.StockInID = s.StockInID
WHERE si.Status='Audited' AND s.ProductID=T.ProductID AND s.SizesID=T.SizesID AND s.ColorsID=T.ColorsID)
,销售总量=(SELECT ISNULL(SUM(o.Quantity),0)
FROM TheBeerHouse.OrderItems o
LEFT JOIN TheBeerHouse.Orders oo ON oo.OrderID = o.OrderID
WHERE oo.ShippingStatus = 'ApplyShipped' AND o.ProductID=T.ProductID AND o.SizesID=T.SizesID AND o.ColorsID=T.ColorsID)
,库存数量 = ISNULL(T.qty,0)
FROM 
cte_qty AS T

GO