https://alidocs.dingtalk.com/i/nodes/AR4GpnMqJzM6mg9ruLybovvAVKe0xjE3?utm_scene=team_space

[步骤]

(@CustomerID int,@OrderStatus varchar(max) ,@Tb_OrderCodes [Tb_StringIds] READONLY)
SELECT
oi.CustomerID,
oi.WareHouseID,
ol.SKU,
SUM(ol.ProductNum + ol.ProductNumUpdate - ol.LockNum) AS UnLockProductNum,
oi.IsStockOrder
FROM RB_OrderInfo oi
INNER JOIN RB_OrderList ol
ON oi.CustomerID = ol.CustomerID AND oi.OrderCode = ol.OrderCode
INNER JOIN RB_WareHouse wh
ON oi.CustomerID = wh.CustomerID AND oi.WareHouseID = wh.ID
INNER JOIN RB_OrderSource os
ON oi.CustomerID = os.CustomerID AND oi.OrderSourceID = os.ID
LEFT JOIN @Tb_OrderCodes t
ON t.Id = oi.OrderCode
WHERE
oi.CustomerID = @CustomerID
AND oi.AddTime >= DATEADD(MONTH, -2, GETDATE())
AND oi.OrderState = 0
AND oi.OrderStatus IN (SELECT CAST(item AS INT) FROM Charsplitforin(@OrderStatus, ','))
AND oi.IsPay = 1
AND ol.PurchaseType < 4
AND (ol.ProductNum + ol.ProductNumUpdate - ol.LockNum) > 0
AND ol.SKU <> '' AND ol.SKU IS NOT NULL
AND oi.WareHouseID > 0
AND wh.WareHouseType = 1
AND os.OrderSourceType <> 20
AND t.Id IS NULL
GROUP BY
oi.CustomerID,
oi.WareHouseID,
ol.SKU,

oi.IsStockOrder

declare @p5 dbo.Tb_StringIdsinsert into @p5 values(N'202608302102249642522')insert into @p5 values(N'202608302102249642523')insert into @p5 values(N'202608302102249642532')insert into @p5 values(N'202608302102249642533')insert into @p5 values(N'202608302102249642539')insert into @p5 values(N'202608302102249642542')insert into @p5 values(N'202608302102249642544')insert into @p5 values(N'202608302102249642549')insert into @p5 values(N'202608302102249642556')exec sp_executesql N'                    SELECT                         oi.CustomerID,                        oi.WareHouseID,                        ol.SKU,                        SUM(ol.ProductNum + ol.ProductNumUpdate - ol.LockNum) AS UnLockProductNum,                        oi.IsStockOrder                    FROM RB_OrderInfo oi                    INNER JOIN RB_OrderList ol                         ON oi.CustomerID = ol.CustomerID AND oi.OrderCode = ol.OrderCode                    INNER JOIN RB_WareHouse wh                         ON oi.CustomerID = wh.CustomerID AND oi.WareHouseID = wh.ID                    INNER JOIN RB_OrderSource os                         ON oi.CustomerID = os.CustomerID AND oi.OrderSourceID = os.ID                    LEFT JOIN @Tb_OrderCodes t                         ON t.Id = oi.OrderCode                    WHERE                         oi.CustomerID = @CustomerID                        AND oi.AddTime >= DATEADD(MONTH, -2, GETDATE())                        AND oi.OrderState = 0                        AND oi.OrderStatus IN (SELECT CAST(item AS INT) FROM Charsplitforin(@OrderStatus, '',''))                        AND oi.IsPay = 1                        AND ol.PurchaseType < 4 AND (ol.ProductNum + ol.ProductNumUpdate - ol.LockNum) > 0                        AND ol.SKU <> '''' AND ol.SKU IS NOT NULL                        AND oi.WareHouseID > 0                        AND wh.WareHouseType = 1                        AND os.OrderSourceType <> 20                        AND t.Id IS NULL                    GROUP BY                         oi.CustomerID,                        oi.WareHouseID,                        ol.SKU,                        oi.IsStockOrder',N'@CustomerID int,@OrderStatus varchar(max) ,@Tb_OrderCodes [Tb_StringIds] READONLY',@CustomerID=1,@OrderStatus='1,6,0',@Tb_OrderCodes=@p5

[结果]

[期望]

补充 nolock

10000