[步骤]
DECLARE @Temp TABLE (
[CustomerID] INT,
[OrderSourceID] INT,
[OrderSourceSKU] NVARCHAR(200),
[SKU] VARCHAR(50),
[SalesNum] INT,
[DailySales] INT,
[SalesDay] SMALLDATETIME,
[OrderNum] INT,
WareHouseID INT,
EstimatedSalesNum INT,
EstimatedSalesNum_3 INT,
NoDiscountOrderNum INT,
CreateOrderNum INT)
INSERT INTO @Temp
([CustomerID],
[OrderSourceID],
[OrderSourceSKU],
[SKU],
[SalesNum],
[DailySales],
[SalesDay],
[OrderNum],
WareHouseID,
EstimatedSalesNum,
EstimatedSalesNum_3,
NoDiscountOrderNum,
CreateOrderNum)
SELECT CustomerID,
OrderSourceID,
SellerSKU,
SKU,
Isnull(Sum(ProductNum), 0) ProductNum,
Isnull(Sum(DailySales), 0) DailySales,
paytime,
Count(0) orderNum,
WareHouseID,
Sum(EstimatedSalesNum) EstimatedSalesNum,
Sum(EstimatedSalesNum_3) EstimatedSalesNum_3,
Isnull(Sum(NoDiscountOrderNum), 0) NoDiscountOrderNum,
Sum(CreateOrderNum) AS CreateOrderNum
FROM (SELECT o.OrderSourceID,
l.SKU,
l.SellerSKU,
Sum(l.ProductNum + l.ProductNumUpdate) AS ProductNum,
Sum(CASE
WHEN o.OrderStatus = 1 THEN 0
ELSE l.ProductNum + l.ProductNumUpdate
END) AS DailySales,
CONVERT(CHAR(10), PayTime, 120) PayTime,
o.CustomerID,
o.OrderCode,
o.WareHouseID,
Sum(CASE
WHEN c.EstimatedSalesVolumeConfigId IS NULL THEN l.ProductNum + l.ProductNumUpdate
WHEN PreferentialRatio > 0
AND o.TotalPrice > 0
AND PromotionDiscountAmount / TotalPrice * 100 > PreferentialRatio THEN 0
WHEN ( CASE
WHEN Len(GroupSKU) > 0 THEN l.GroupProductNum
ELSE l.ProductNum + l.ProductNumUpdate
END ) > c.ExcessProductQuantity
AND c.EstimatedSalesVolumeType = 1 THEN 0
WHEN ( CASE
WHEN Len(GroupSKU) > 0 THEN l.GroupProductNum
ELSE l.ProductNum + l.ProductNumUpdate
END ) > c.ExcessProductQuantity
AND c.EstimatedSalesVolumeType = 2 THEN c.EstimatedSalesVolume
ELSE l.ProductNum + l.ProductNumUpdate
END) EstimatedSalesNum,
Sum(CASE
WHEN l.ProductNum + l.ProductNumUpdate > 3 THEN 3
ELSE l.ProductNum + l.ProductNumUpdate
END) EstimatedSalesNum_3,
( CASE
WHEN PromotionDiscountAmount > 0 THEN 0
ELSE 1
END ) NoDiscountOrderNum,
Sum(CASE
WHEN o.DownLoadUpdateTime = '2000-01-01' THEN 1
ELSE 0
END) AS CreateOrderNum
FROM RB_OrderInfo o WITH(nolock)
LEFT JOIN RB_OrderList l WITH(nolock)
ON o.CustomerID = l.CustomerID
AND o.OrderCode = l.OrderCode
LEFT JOIN RB_OrderSource s WITH(nolock)
ON o.CustomerID = s.CustomerID
AND o.OrdersourceID = s.ID
LEFT JOIN RB_Order_EstimatedSalesVolume_Config c(nolock)
ON s.CustomerID = c.CustomerId
AND s.OrderSourceType = c.OrderSourceType
AND c.IsActived = 1
WHERE ( o.IsPay = 1
OR IsFBAOrder = 1 )
AND o.PayTime >= '2026-06-09'
AND o.PayTime < '2026-06-14'
AND Datediff(YEAR, o.PayTime, '2079-01-01') > 0
AND o.OrderStatus <> 2
AND Len(sku) > 0
AND o.IsStockOrder = 0
and o.CustomerID=1
AND ProductNum + ProductNumUpdate > 0
GROUP BY o.OrderSourceID,
l.SKU,
l.SellerSKU,
CONVERT(CHAR(10), PayTime, 120),
o.CustomerID,
o.OrderCode,
o.WareHouseID,
o.PromotionDiscountAmount)a
GROUP BY OrderSourceID,
SKU,
SellerSKU,
paytime,
CustomerID,
WareHouseID; --组合SKU销量
INSERT INTO @Temp
([CustomerID],
[OrderSourceID],
[OrderSourceSKU],
[SKU],
[SalesNum],
[DailySales],
[SalesDay],
[OrderNum],
WareHouseID,
EstimatedSalesNum,
EstimatedSalesNum_3,
NoDiscountOrderNum,
CreateOrderNum)
SELECT CustomerID,
OrderSourceID,
SellerSKU,
GroupSKU,
Isnull(Sum(GroupProductNum + GroupProductNumUpdate), 0) ProductNum,
Sum(CASE
WHEN OrderStatus = 1 THEN 0
ELSE GroupProductNum + GroupProductNumUpdate
END) AS DailySales,
paytime,
Count(0) orderNum,
WareHouseID,
Sum(EstimatedSalesNum) EstimatedSalesNum,
Sum(EstimatedSalesNum_3) EstimatedSalesNum_3,
Isnull(Sum(NoDiscountOrderNum), 0) NoDiscountOrderNum,
Sum(CreateOrderNum) AS CreateOrderNum
FROM (SELECT o.OrderSourceID,
l.GroupSKU,
l.SellerSKU,
l.GroupProductNum,
l.GroupProductNumUpdate,
CONVERT(CHAR(10), PayTime, 120) PayTime,
o.CustomerID,
o.OrderCode,
o.WareHouseID,
o.OrderStatus,
Min(CASE
WHEN c.EstimatedSalesVolumeConfigId IS NULL THEN l.GroupProductNum
WHEN PreferentialRatio > 0
AND o.TotalPrice > 0
AND PromotionDiscountAmount / TotalPrice * 100 > PreferentialRatio THEN 0
WHEN l.GroupProductNum > c.ExcessProductQuantity
AND c.EstimatedSalesVolumeType = 1 THEN 0
WHEN l.GroupProductNum > c.ExcessProductQuantity
AND c.EstimatedSalesVolumeType = 2 THEN c.EstimatedSalesVolume
ELSE l.GroupProductNum
END) EstimatedSalesNum,
Min(CASE
WHEN l.GroupProductNum + l.GroupProductNumUpdate > 3 THEN 3
ELSE l.GroupProductNum + l.GroupProductNumUpdate
END) EstimatedSalesNum_3,
( CASE
WHEN PromotionDiscountAmount > 0 THEN 0
ELSE 1
END ) NoDiscountOrderNum,
Sum(CASE
WHEN o.DownLoadUpdateTime = '2000-01-01' THEN 1
ELSE 0
END) AS CreateOrderNum
FROM RB_OrderInfo o WITH(nolock)
LEFT JOIN RB_OrderList l WITH(nolock)
ON o.CustomerID = l.CustomerID
AND o.OrderCode = l.OrderCode
LEFT JOIN RB_OrderSource s WITH(nolock)
ON o.CustomerID = s.CustomerID
AND o.OrdersourceID = s.ID
LEFT JOIN RB_Order_EstimatedSalesVolume_Config c(nolock)
ON s.CustomerID = c.CustomerId
AND s.OrderSourceType = c.OrderSourceType
AND c.IsActived = 1
WHERE ( o.IsPay = 1
OR IsFBAOrder = 1 )
AND o.PayTime >= '2026-06-09'
AND o.PayTime < '2026-06-14'
AND o.OrderStatus <> 2
AND Len(sku) > 0
AND o.IsStockOrder = 0
and o.CustomerID=1
AND Len(GroupSKU) > 0
GROUP BY o.OrderSourceID,
l.GroupSKU,
l.SellerSKU,
CONVERT(CHAR(10), PayTime, 120),
o.CustomerID,
o.OrderCode,
o.WareHouseID,
l.GroupProductNum,
l.GroupProductNumUpdate,
o.PromotionDiscountAmount,
o.OrderStatus)a
GROUP BY OrderSourceID,
GroupSKU,
SellerSKU,
paytime,
CustomerID,
WareHouseID;
UPDATE d
SET [SalesNum] = Isnull(t.[SalesNum], 0),
[DailySales] = Isnull(t.[DailySales], 0),
[OrderNum] = Isnull(t.[OrderNum], 0),
EstimatedSalesNum = Isnull(t.EstimatedSalesNum, 0),
EstimatedSalesNum_3 = Isnull(t.EstimatedSalesNum_3, 0),
NoDiscountOrderNum = Isnull(t.NoDiscountOrderNum, 0),
CreateOrderNum = Isnull(t.CreateOrderNum, 0),
UpdateTime = Getdate()
FROM RB_Report_OrderSourceSKUDay d (nolock)
LEFT JOIN @Temp t
ON t.CustomerID = d.CustomerID
AND t.OrderSourceID = d.OrderSourceID
AND t.OrderSourceSKU = d.OrderSourceSKU
AND t.WareHouseID = d.WareHouseID
AND t.SalesDay = d.SalesDay
AND t.SKU = d.SKU
WHERE d.SalesDay >= '2026-06-09'
AND d.SalesDay < '2026-06-14'
INSERT INTO RB_Report_OrderSourceSKUDay
([CustomerID],
[OrderSourceID],
[OrderSourceSKU],
[SKU],
[SalesNum],
[DailySales],
[SalesDay],
[OrderNum],
WareHouseID,
EstimatedSalesNum,
EstimatedSalesNum_3,
NoDiscountOrderNum,
AddTime,
UpdateTime,
CreateOrderNum)
SELECT t.[CustomerID],
t.[OrderSourceID],
t.[OrderSourceSKU],
t.[SKU],
t.[SalesNum],
t.[DailySales],
t.[SalesDay],
t.[OrderNum],
t.WareHouseID,
t.EstimatedSalesNum,
t.EstimatedSalesNum_3,
t.NoDiscountOrderNum,
Getdate(),
Getdate(),
t.CreateOrderNum
FROM @Temp t
LEFT JOIN RB_Report_OrderSourceSKUDay d (nolock)
ON t.CustomerID = d.CustomerID
AND t.OrderSourceID = d.OrderSourceID
AND t.OrderSourceSKU = d.OrderSourceSKU
AND t.WareHouseID = d.WareHouseID
AND t.SalesDay = d.SalesDay
AND t.SKU = d.SKU
WHERE d.ID IS NULL; ;
[结果]
[期望]
建议 补齐 and o.CustomerID=1, 否则无法用上索引
已调整提交