[步骤]
771357771356
线上 RB_ReportApply_Task ID 771355 超过半小时无法导出 阻塞导出队列
select ROW_NUMBER() over(order by a.SKU) '编号', a.ProductGroupSKU '母体ID', a.SKU, a.OrderSourceSKU '渠道SKU', a.ClientSKU '自定义SKU', a.ASINorItemID 'ASIN', a.SalesNum '销售数量', a.WareHouseName '仓库名称', a.GoodNum '库存数量', convert(varchar(4), a.SalesYear) + '年' + convert(varchar(2), a.SalesMonth) + '月' '时间', a.OrderNum '订单数', a.OrderSourceName '订单来源', a.ProductName '产品名称', a.ProductNameCN '产品中文名' from( select ROW_NUMBER() over(PARTITION by a.CustomerID,a.OrderSourceID,a.SKU,a.OrderSourceSKU,a.WareHouseID, a.SalesYear, a.SalesMonth order by a.CustomerID,a.OrderSourceID,a.SKU,a.OrderSourceSKU,a.WareHouseID, a.SalesYear, a.SalesMonth) SortNum, a.ProductGroupSKU, a.SKU, a.OrderSourceSKU, a.ClientSKU, b.ASINorItemID, a.SalesNum, a.WareHouseName, a.GoodNum, a.SalesYear, a.SalesMonth, a.OrderNum, a.OrderSourceName, a.ProductName, a.ProductNameCN from dbo.View_RB_Report_OrderSourceSKUMonth a WITH(NOLOCK) LEFT JOIN dbo.RB_Amazon_Product_Base(NOLOCK) b ON b.CustomerID = a.CustomerID AND b.OrderSourceID = a.OrderSourceID AND b.SKU = a.SKU AND b.OrderSourceSKU = a.OrderSourceSKU AND b.IsOffShelf IN(0, 1, 2) AND b.IsDelete=0 AND b.ASINorItemID != '' where (DATEDIFF(MONTH,CAST(a.SalesYear AS NVARCHAR(4)) + '-' + CAST(a.SalesMonth AS NVARCHAR(2))+'-1','2023-3-1')<=0) AND (DATEDIFF(MONTH,CAST(a.SalesYear AS NVARCHAR(4)) + '-' + CAST(a.SalesMonth AS NVARCHAR(2))+'-1','2024-3-1')>=0) AND (a.OrderSourceID IN(9948)) AND (a.CustomerID=1) AND (a.SalesNum>0) AND (a.OrderSourceID IN (SELECT OrderSourceID FROM RB_OrderSource_SaleAdmin (NOLOCK) WHERE AdminID=5841)) ) a where SortNum = 1
[结果]
[期望]
从业务角度改造SQL 或优化性能