|
实现效果：
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
|