php mysql库存_视图显示库存余量

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

实现效果：

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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！