[步骤]
exec sp_executesql N' selECT t.CustomerID ,t.OrderNum, CAST(t.dt AS DATETIME) dt, t.OrderSourceType, t.DepartmentName, t.SalesUSD, t.SinglePrice, t.TotalActivityMinusPrice from ( SELECT oi.CustomerID, SUM ( CASE WHEN TotalPrice = 0 AND oi.DownLoadUpdateTime= ''2000-01-01'' THEN 0 ELSE 1 END ) AS OrderNum, CONVERT(VARCHAR(100), oi.AddTime, 23) dt, os.OrderSourceType OrderSourceType, DeptLogic.DepartmentName AS DepartmentName, (SUM ( TotalPrice ) / (CASE WHEN SUM(CASE WHEN TotalPrice = 0 THEN 0 ELSE 1 END) = 0 THEN 1 ELSE SUM(CASE WHEN TotalPrice = 0 THEN 0 ELSE 1 END) END) * SUM ( CASE WHEN TotalPrice = 0 AND oi.DownLoadUpdateTime= ''2000-01-01'' THEN 0 ELSE 1 END ))AS SalesUSD, SUM ( TotalPrice ) / (CASE WHEN SUM(CASE WHEN TotalPrice = 0 THEN 0 ELSE 1 END) = 0 THEN 1 ELSE SUM(CASE WHEN TotalPrice = 0 THEN 0 ELSE 1 END) END) AS SinglePrice, SUM( oi.TotalActivityMinusPrice ) as TotalActivityMinusPrice FROM ( SELECT oi.CustomerID, oi.OrderSourceID, oi.OrderCode, oi.TotalPrice / (CASE WHEN ExchangeUSD = 0 THEN 1 ELSE ExchangeUSD END) as TotalPrice, oi.DownLoadUpdateTime, oi.ExchangeUSD, oi.AddTime, (CASE WHEN oi.TotalPrice = 0 THEN 0 ELSE SUM(ol.ActivityMinusPrice/ (CASE WHEN ExchangeUSD = 0 THEN 1 ELSE ExchangeUSD END) ) END) as TotalActivityMinusPrice,MAX(ol.BusinessAdminID) SaleAdminID FROM dbo.RB_OrderInfo oi WITH (NOLOCK) LEFT JOIN RB_OrderList ol WITH (NOLOCK) ON oi.CustomerID = ol.CustomerID and oi.OrderCode = ol.OrderCode WHERE oi.CustomerID = 1 AND oi.AddTime >= @DtStart AND oi.AddTime <= @DtEnd AND oi.IsStockOrder = 0 AND IsSystem = 0 AND ( ( IsPay = 1 AND IsFBAOrder = 0 ) OR IsFBAOrder = 1 ) AND OrderStatus <> 2 GROUP BY oi.CustomerID, oi.OrderSourceID, oi.OrderCode, oi.TotalPrice, oi.DownLoadUpdateTime, oi.ExchangeUSD, oi.AddTime )oi inner JOIN RB_OrderSource (NOLOCK) os ON oi.OrderSourceID = os.ID AND oi.CustomerID= os.CustomerID INNER JOIN RB_Admin (NOLOCK) ad ON oi.CustomerID=ad.CustomerID AND oi.SaleAdminID=ad.ID -- 使用 CROSS APPLY 处理字符串逻辑，相当于给每一行生成一个临时计算表 CROSS APPLY ( SELECT -- 1. 统一清洗后的路径 LTRIM(RTRIM( REPLACE(REPLACE(REPLACE(REPLACE(ad.UserFullPathName, ''全公司 - '', ''''), ''全公司 > '', ''''), '' - '', ''>''), '' > '', ''>'') )) AS CleanPath ) AS Cleaner CROSS APPLY ( SELECT -- 2. 基于清洗后的路径计算一级部门 CASE WHEN CHARINDEX(''>'', Cleaner.CleanPath) > 0 THEN LEFT(Cleaner.CleanPath, CHARINDEX(''>'', Cleaner.CleanPath) - 1) ELSE Cleaner.CleanPath END AS FirstLevelDept, -- 3. 基于清洗后的路径计算二级部门 CASE WHEN CHARINDEX(''>'', Cleaner.CleanPath) > 0 THEN CASE WHEN CHARINDEX(''>'', Cleaner.CleanPath, CHARINDEX(''>'', Cleaner.CleanPath) + 1) > 0 THEN SUBSTRING( Cleaner.CleanPath, CHARINDEX(''>'', Cleaner.CleanPath) + 1, CHARINDEX(''>'', Cleaner.CleanPath, CHARINDEX(''>'', Cleaner.CleanPath) + 1) - CHARINDEX(''>'', Cleaner.CleanPath) - 1 ) ELSE SUBSTRING(Cleaner.CleanPath, CHARINDEX(''>'', Cleaner.CleanPath) + 1, LEN(Cleaner.CleanPath)) END ELSE NULL END AS SecondLevelDept ) AS ad2 -- 【核心优化】在这里统一计算 DepartmentName，主查询直接取用 CROSS APPLY ( SELECT LEFT( CASE WHEN os.OrderSourceType <= 0 THEN '''' WHEN ad2.FirstLevelDept IS NULL THEN REPLACE(REPLACE(ad.UserFullPathName, ''全公司 - '', ''''), ''全公司 > '', '''') WHEN ad.UserFullPath LIKE ''%{500}%'' THEN ad2.FirstLevelDept + CASE WHEN os.OrderSourceName LIKE ''%VC账号%'' THEN ''VC'' ELSE ''非VC'' END WHEN ad.UserFullPath LIKE ''%{520}%'' THEN ad2.FirstLevelDept + ''-'' + ISNULL(ad2.SecondLevelDept, '''') ELSE ad2.FirstLevelDept END,13) AS DepartmentName ) AS DeptLogic GROUP BY oi.CustomerID, CONVERT(VARCHAR(100), oi.AddTime, 23),os.OrderSourceType,DeptLogic.DepartmentName ) t',N'@DtStart datetime,@DtEnd datetime',@DtStart='2026-04-03 00:00:00',@DtEnd='2026-04-10 23:59:59'[结果]
[期望]
改成读库 或者 错开 6点半 到 7点 这个 时间段