影响范围

自定义报告亚马逊上架产品销量报告V5（指定上架人员查询），性能调优后是否能正常使用

提交记录

自测报告

1、导出报告(优化后导出2个月大概在10分钟左右，之前1个月则经常超时)

2、执行导出服务

3、导出后下载报告

注：上线后该自定义报告需要重新配置，参数@OrderSourceID类型需改为字符串，详细Sql配置如下：

--DECLARE @CustomerID INT = 1
--DECLARE @Days INT = 60
--DECLARE @Day INT = 60
--DECLARE @addStartDate DATETIME = '2022-09-01'
--DECLARE @addEndDate DATETIME = '2022-10-01'


-- 删除临时表
DROP TABLE IF EXISTS #TempAddedSale,#tabOrderSourceSKU
-- 创建临时表 #TempAddedSale
CREATE TABLE #TempAddedSale
(
  ID [INT] NOT NULL IDENTITY(1, 1),
  CustomerID INT,
  OrderCode NVARCHAR(50) COLLATE Chinese_PRC_CI_AS,
  OrderSourceID INT,
  ASINorItemID NVARCHAR(50) COLLATE Chinese_PRC_CI_AS,
  SKU VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
  SellerSKU VARCHAR(50) COLLATE Chinese_PRC_CI_AS,
  BusinessAdminID INT,
  SaleCount INT,
  SalePrice MONEY,
  ProfitPrice MONEY,
  SingleDays INT,
  AddTime DATETIME,
  FirstPayTime DATETIME,
  OrderSourceCountry NVARCHAR(50) COLLATE Chinese_PRC_CI_AS,
  IsFBA INT,
  GroupSKU VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
  GroupSaleCount INT,
  OnTheWayType NVARCHAR(50) COLLATE Chinese_PRC_CI_AS,
  ListingBusinessTeamName NVARCHAR(50),
  [isupdate] INT
) -- 创建索引
CREATE INDEX IX_ID ON #TempAddedSale (ID) INCLUDE (CustomerID, OrderSourceID, OrderCode, ASINorItemID)
CREATE INDEX IX_ID1 ON #TempAddedSale (CustomerID, BusinessAdminID, SKU, IsFBA, OnTheWayType, OrderSourceCountry)
-- 创建临时表 #tabOrderSourceSKU
CREATE TABLE #tabOrderSourceSKU
(
  ID INT IDENTITY(1, 1),
  CustomerID INT,
  OrderSourceSKU NVARCHAR(500) COLLATE Chinese_PRC_CI_AS,
  SKU NVARCHAR(50) COLLATE Chinese_PRC_CI_AS,
  OrderSourceID INT,
  ASINorItemID NVARCHAR(500) COLLATE Chinese_PRC_CI_AS,
  AdminID INT,
  addtime DATETIME,
  firstaddtime DATETIME,
  IsFBA BIT,
  SellerType INT,
  isGroup INT,
  OnTheWayType NVARCHAR(50) COLLATE Chinese_PRC_CI_AS,
  BusinessAdminID INT,
  ListingBusinessTeamName NVARCHAR(50),
  IsWithSell INT,
  [isupdate] INT
) -- 创建索引
CREATE INDEX IX_ID ON #tabOrderSourceSKU (ID) INCLUDE (CustomerID, addtime)
--create index IX_addtime on #tabOrderSourceSKU(AddTime) include(ASINorItemID,SKU,CustomerID)
CREATE INDEX IX_addtime1 ON #tabOrderSourceSKU (CustomerID, ASINorItemID, SKU, addtime)
-- 临时表 #tabOrderSourceSKU 加入Amazon上传队列数据 (包括自主上架和跟卖的数据)
INSERT INTO
  #tabOrderSourceSKU
SELECT
  ra.CustomerID,
  ra.OrderSourceSKU,
  ra.SKU,
  ra.OrderSourceID,
  ra.ASINorItemID,
  ra.AddAdminID,
  ra.PublishTime,
  ra.PublishTime,
  ra.IsFBA,
  ra.SellerType,
  0 AS isGroup,
  CASE
    ra.OnShelfMode
    WHEN 1 THEN '自主上架' -- 一键上架
    WHEN 2 THEN '自主上架' -- 草稿上架
    WHEN 3 THEN '表格上架'
    WHEN 4 THEN '关联导入'
    WHEN 5 THEN '批量跟卖'
    WHEN 6 THEN '凤凰重刊'
    WHEN 7 THEN '复制上架'
    WHEN 8 THEN '智能刊登'
    ELSE '其他'
  END,
  ra.BusinessAdminID,
  ra.BusinessTeamName,
  0,
  --CASE WHEN ra.OnShelfMode=5 THEN 1 ELSE 0 END,
  0
FROM
  dbo.RB_Amazon_Product_Base ra WITH (NOLOCK)
  LEFT JOIN RB_Product_GroupSku g (NOLOCK) ON ra.SKU = g.GroupSku
  AND ra.CustomerID = g.CustomerID
  {{ INNER JOIN (SELECT CONVERT(INT,  item) OrderCode FROM Charsplitforin(@OrderSourceID,',')) x ON ra.OrderSourceID=x.OrderCode }}
WHERE
  ra.PublishTime BETWEEN @addStartDate
  AND DATEADD(DAY, @Day, @addEndDate) --AND ra.ASINorItemID = 'B09CM2QZ3K'
  --当月上架listing
  --AND ra.IsDelete = 0
  --AND ra.IsOffShelf = 0
  AND ra.CustomerID = @CustomerID
  -- AND {{ ra.OrderSourceID in (@OrderSourceID) }}
  AND ra.OrderSourceID IN (
    SELECT
      ID
    FROM
      RB_OrderSource (NOLOCK)
    WHERE
      OrderSourceType = 1
      AND CustomerID = @CustomerID
  );-- 添加渠道SKU数据表中的sku数据
  /*
  INSERT INTO #tabOrderSourceSKU
  SELECT
  pos.CustomerID,
  pos.OrderSourceSKU,
  pos.SKU,
  pos.OrderSourceID,
  pos.ASINorItemID,
  pos.AddAdminID,
  pos.AddTime,
  pos.AddTime firstaddtime,
  pos.IsFba,
  pos.SellerType,
  0 AS isGroup,
  '未知',
  pos.BusinessAdminID,
  NULL ,
  2, --CASE WHEN ra.OnShelfMode=5 THEN 1 ELSE 0 END,
  0
  
  FROM srv_caiwushenji.Summary_db.dbo.rb_product_ordersourcesku1_cfs pos WITH (NOLOCK)
  LEFT JOIN (SELECT DISTINCT OrderSourceSKU,CustomerID FROM RB_Amazon_Product_Base WHERE CustomerID=@CustomerID) b ON b.CustomerID = pos.CustomerID AND b.OrderSourceSKU = pos.OrderSourceSKU
  
  WHERE
  pos.CustomerID = @CustomerID
  --AND pos.ASINorItemID = 'B096TPW7L8'
  AND pos.AddTime BETWEEN @addStartDate AND DATEADD(DAY, @Day, @addEndDate)
  AND pos.OrderSourceID IN(SELECT ID FROM RB_OrderSource WITH (NOLOCK) WHERE OrderSourceType = 1 AND CustomerID = @CustomerID)
  AND b.OrderSourceSKU IS NULL
  */
  -- 更新上架方式
  /*
  UPDATE #tabOrderSourceSKU
  SET  OnTheWayType = '自主上架'
  FROM #tabOrderSourceSKU a
  INNER JOIN AAA_amazon0121 g (NOLOCK) ON a.OrderSourceSKU = g.SellerSKU AND a.OrderSourceID=g.OrderSourceID
  WHERE a.CustomerID=1 AND a.OnTheWayType='未知'
  */
  -- 更新组SKU
UPDATE
  #tabOrderSourceSKU
SET
  isGroup = 1
FROM
  #tabOrderSourceSKU a
  INNER JOIN RB_Product_GroupSku g (NOLOCK) ON a.SKU = g.GroupSku
  AND a.CustomerID = g.CustomerID
WHERE
  a.CustomerID = 1 ------更新转FBA发货Listing的上架人员与首次上架时间---
UPDATE
  #tabOrderSourceSKU
SET
  #tabOrderSourceSKU.AdminID = b.AddAdminID,       --自主上架人员
  #tabOrderSourceSKU.firstaddtime = b.PublishTime, --自主上架人员
  #tabOrderSourceSKU.[isupdate] = 1
FROM
  #tabOrderSourceSKU
  INNER JOIN (
    SELECT
      a.ID,
      b.AddAdminID,
      a.ASINorItemID,
      a.SKU,
      b.PublishTime,
      RANK() OVER(
        PARTITION BY a.ID
        ORDER BY
          b.PublishTime ASC
      ) rank
    FROM
      #tabOrderSourceSKU a
      INNER JOIN dbo.RB_Amazon_Product_Base (NOLOCK) b ON b.CustomerID = a.CustomerID
      AND a.ASINorItemID = b.ASINorItemID
      AND b.SKU = a.SKU
    WHERE
      a.CustomerID = 1
      AND (
        a.IsFBA = 1
        OR a.OnTheWayType = '批量跟卖'
      )
      AND b.IsFBA = 0
      AND a.addtime >= b.PublishTime
      AND a.AdminID <> b.AddAdminID
      AND DATEDIFF(DAY, b.PublishTime, a.addtime) < 60
  ) b ON b.ID = #tabOrderSourceSKU.ID
WHERE
  b.RANK = 1
DELETE FROM
  #tabOrderSourceSKU WHERE firstaddtime > @addEndDate
DELETE FROM
  #tabOrderSourceSKU WHERE AdminID != @AdminID
  ------筛选订单数据保存到临时表-------
INSERT INTO
  #TempAddedSale
  (
    CustomerID,
    OrderCode,
    OrderSourceID,
    SKU,
    SellerSKU,
    ASINorItemID,
    BusinessAdminID,
    SaleCount,
    SingleDays,
    AddTime,
    FirstPayTime,
    OrderSourceCountry,
    IsFBA,
    GroupSKU,
    GroupSaleCount,
    OnTheWayType,
    [isupdate]
  )
SELECT
  info.CustomerID,
  info.OrderCode,
  info.OrderSourceID,
  (
    CASE
      WHEN list.GroupSKU <> '' THEN list.GroupSKU
      ELSE list.SKU
    END
  ) AS SKU,
  list.SellerSKU,
  ossku.ASINorItemID,
  ossku.AdminID,
  (list.ProductNum + list.[ProductNumUpdate]) AS saleCount,
  DATEDIFF(DAY, (ossku.addtime), (PayTime)) AS SingleDays,
  ossku.addtime,
  PayTime,
  source.OrderSourceCountry,
  ossku.IsFBA,
  list.GroupSKU,
  (
    ISNULL(list.GroupProductNum, 0) + ISNULL(list.[GroupProductNumUpdate], 0)
  ) AS GroupSaleCount,
  ossku.OnTheWayType,
  ossku.[isupdate]
FROM
  RB_OrderInfo info WITH (NOLOCK)
  INNER JOIN RB_OrderSource source WITH (NOLOCK) ON info.OrderSourceID = source.ID
  AND info.CustomerID = source.CustomerID
  INNER JOIN RB_OrderList list WITH (NOLOCK) ON info.OrderCode = list.OrderCode
  AND info.CustomerID = list.CustomerID
  INNER JOIN #tabOrderSourceSKU AS ossku (NOLOCK)
  ON list.SellerSKU = ossku.OrderSourceSKU
  AND (
    list.SKU = ossku.SKU
    OR list.GroupSKU = ossku.SKU
  )
  AND info.OrderSourceID = ossku.OrderSourceID
  AND info.CustomerID = ossku.CustomerID
  {{ INNER JOIN (SELECT CONVERT(INT,  item) OrderCode FROM Charsplitforin(@OrderSourceID,',')) x ON info.OrderSourceID=x.OrderCode }}
WHERE
  info.IsPay = 1
  AND info.IsSystem = 0
  AND info.IsStockOrder = 0 --AND info.OrderStatus != 2
  AND source.OrderSourceType = 1
  AND info.CustomerID = @CustomerID
  -- AND {{ info.OrderSourceID in (@OrderSourceID) }}
  AND info.PayTime BETWEEN CONVERT(VARCHAR(12), ossku.firstaddtime, 23) + ' 00:00:00'
  AND CONVERT(
    VARCHAR(12),
    DATEADD(DAY, @Days, ossku.firstaddtime),
    23
  ) + ' 23:59:59'
  AND ossku.firstaddtime BETWEEN @addStartDate
  AND @addEndDate;------更新临时表中的销售价格和利润-------
UPDATE
  #TempAddedSale
SET
  #TempAddedSale.ProfitPrice = pf.Profits,
  #TempAddedSale.SalePrice = pf.Incomes
FROM
  #TempAddedSale temp
  INNER JOIN (
    SELECT
      SUM(Profits) AS Profits,
      SUM(Incomes) AS Incomes,
      OrderSourceID,
      ASINorItemID,
      CustomerID,
      OrderCode
    FROM
      (
        SELECT
          MAX(ros.Profits) AS Profits,
          MAX(ros.Incomes) AS Incomes,
          pos.OrderSourceID,
          pos.ASINorItemID,
          pos.CustomerID,
          ros.OrderCode
        FROM
          #tabOrderSourceSKU pos (NOLOCK)
          LEFT JOIN RB_Report_OrderSettlement ros (NOLOCK) ON pos.OrderSourceID = ros.OrderSourceID
          AND pos.CustomerID = ros.CustomerID
          AND pos.OrderSourceSKU = ros.SellerSKU
        WHERE
          ros.ID IS NOT NULL
        GROUP BY
          pos.OrderSourceID,
          pos.ASINorItemID,
          pos.CustomerID,
          OrderCode,
          ros.ID
      ) xx
    GROUP BY
      OrderSourceID,
      ASINorItemID,
      CustomerID,
      OrderCode
  ) AS pf ON temp.CustomerID = pf.CustomerID
  AND temp.OrderSourceID = pf.OrderSourceID
  AND temp.OrderCode = pf.OrderCode
  AND temp.ASINorItemID = pf.ASINorItemID;------删除临时表中重复数据-------
DELETE FROM
  #TempAddedSale
WHERE
  ID IN (
    SELECT
      ID
    FROM
      (
        SELECT
          ROW_NUMBER() OVER (
            PARTITION BY CustomerID,
            OrderCode,
            OrderSourceID,
            ASINorItemID,
            SellerSKU
            ORDER BY
              ID ASC
          ) rowid,
          ID,
          CustomerID,
          OrderCode,
          OrderSourceID,
          ASINorItemID,
          SellerSKU
        FROM
          #TempAddedSale (NOLOCK)
        WHERE
          GroupSKU <> ''
      ) b
    WHERE
      rowid > 1
  )
  AND GroupSKU <> ''
  AND CustomerID = @CustomerID;------删除临时表中重复数据-------
DELETE FROM
  #TempAddedSale
WHERE
  ID IN (
    SELECT
      ID
    FROM
      (
        SELECT
          ROW_NUMBER() OVER (
            PARTITION BY OrderCode,
            CustomerID,
            OrderSourceID,
            ASINorItemID,
            SKU,
            SellerSKU
            ORDER BY
              ID ASC
          ) rowid,
          ID,
          OrderCode,
          CustomerID,
          OrderSourceID,
          ASINorItemID,
          SKU,
          SellerSKU
        FROM
          #TempAddedSale (NOLOCK)
      ) b
    WHERE
      rowid > 1
  );
UPDATE
  #TempAddedSale
SET
  #TempAddedSale.ListingBusinessTeamName = b.BusinessTeamName
FROM
  #TempAddedSale AS a
  INNER JOIN RB_Amazon_Product_Base AS b (NOLOCK) ON b.OrderSourceSKU = a.SellerSKU
  AND b.SKU = a.SKU
  AND b.OrderSourceID = a.OrderSourceID
  AND b.CustomerID = a.CustomerID;-----------按照上架人员统计-----------
SELECT
  aaa.UserName AS 上架人员,
  aaa.Post AS 上架人员岗位,
  aaa.BusinessTeamName AS 上架人员销售小组,
  (
    CASE
      aaa.IsFBA
      WHEN 0 THEN '自发货'
      ELSE 'FBA发货'
    END
  ) AS 发货类型,
  aaa.OnTheWayType AS 上架方式,
  销售数量,
  [销售额(RMB)],
  [估算利润(RMB)],
  上架listing数量,
  出单listing数量,
  CASE
    WHEN 上架listing数量 != 0 THEN CONVERT(FLOAT, 出单listing数量) / 上架listing数量
    ELSE 0
  END AS 上架人员listing出单率,
  上架SKU数量,
  出单SKU数量,
  CASE
    WHEN 上架SKU数量 != 0 THEN CONVERT(FLOAT, 出单SKU数量) / 上架SKU数量
    ELSE 0
  END AS 上架人员SKU出单率,
  平均出单天数
FROM
  (
    SELECT
      TOP 100 PERCENT CustomerID,
      AdminID AS BusinessAdminID,
      SUM(
        CASE
          WHEN IsWithSell = 1 THEN 0
          ELSE 1
        END
      ) AS 上架listing数量,
      ISNULL(UserName, '') AS UserName,
      Post,
      BusinessTeamName,
      IsFBA,
      OnTheWayType
    FROM
      (
        SELECT
          ra.CustomerID,
          AdminID,
          ISNULL(UserName, '') AS UserName,
          ISNULL(RB_Admin.Post, '') Post,
          ISNULL(rbt.BusinessTeamName, '未指定') BusinessTeamName,
          COUNT(0) AS countNumber,
          ra.IsFBA,
          ra.OnTheWayType,
          ra.IsWithSell
        FROM
          #tabOrderSourceSKU ra (NOLOCK)
          LEFT JOIN RB_Admin WITH (NOLOCK) ON ra.AdminID = RB_Admin.ID
          AND ra.CustomerID = RB_Admin.CustomerID
          LEFT JOIN RB_BusinessTeam rbt WITH (NOLOCK) ON RB_Admin.BusinessTeamID = rbt.ID
          AND RB_Admin.CustomerID = rbt.CustomerID
        WHERE
          ra.CustomerID = @CustomerID
          AND ra.ASINorItemID != ''
        GROUP BY
          ra.CustomerID,
          ra.OrderSourceID,
          ra.OrderSourceSKU,
          ra.SKU,
          AdminID,
          ISNULL(RB_Admin.Post, ''),
          ISNULL(UserName, ''),
          BusinessTeamName,
          ra.IsFBA,
          ra.OnTheWayType,
          ra.IsWithSell
      ) a
    GROUP BY
      CustomerID,
      AdminID,
      ISNULL(UserName, ''),
      Post,
      BusinessTeamName,
      IsFBA,
      OnTheWayType
    ORDER BY
      AdminID,
      IsFBA,
      OnTheWayType
  ) aaa
  LEFT JOIN (
    SELECT
      CustomerID,
      BusinessAdminID,
      SUM(销售数量) AS 销售数量,
      SUM([销售额(RMB)]) AS [销售额(RMB)],
      SUM([估算利润(RMB)]) AS [估算利润(RMB)],
      SUM(最早出单天数) / COUNT(0) AS 平均出单天数,
      COUNT(0) AS 出单listing数量,
      IsFBA,
      OnTheWayType
    FROM
      (
        SELECT
          CustomerID,
          BusinessAdminID,
          SUM(
            CASE
              WHEN GroupSKU <> '' THEN GroupSaleCount
              ELSE SaleCount
            END
          ) AS 销售数量,
          SUM(SalePrice) AS [销售额(RMB)],
          SUM(ProfitPrice) AS [估算利润(RMB)],
          MIN(SingleDays) AS 最早出单天数,
          IsFBA,
          OnTheWayType
        FROM
          #TempAddedSale
        GROUP BY
          CustomerID,
          BusinessAdminID,
          (
            CASE
              WHEN GroupSKU <> '' THEN GroupSKU
              ELSE SKU
            END
          ),
          SellerSKU,
          IsFBA,
          OnTheWayType
      ) b
    GROUP BY
      CustomerID,
      BusinessAdminID,
      IsFBA,
      OnTheWayType
  ) bbb ON aaa.BusinessAdminID = bbb.BusinessAdminID
  AND aaa.CustomerID = bbb.CustomerID
  AND aaa.IsFBA = bbb.IsFBA
  AND aaa.OnTheWayType = bbb.OnTheWayType
  LEFT JOIN (
    SELECT
      TOP 100 PERCENT CustomerID,
      AdminID AS BusinessAdminID,
      SUM(
        CASE
          WHEN IsWithSell = 1 THEN 0
          ELSE 1
        END
      ) AS 上架SKU数量,
      ISNULL(UserName, '') AS UserName,
      BusinessTeamName,
      IsFBA,
      OnTheWayType
    FROM
      (
        SELECT
          ra.CustomerID,
          AdminID,
          ISNULL(UserName, '') AS UserName,
          ISNULL(rbt.BusinessTeamName, '未指定') BusinessTeamName,
          ra.IsFBA,
          ra.OnTheWayType,
          ra.IsWithSell
        FROM
          #tabOrderSourceSKU ra (NOLOCK)
          LEFT JOIN RB_Admin WITH (NOLOCK) ON ra.AdminID = RB_Admin.ID
          AND ra.CustomerID = RB_Admin.CustomerID
          LEFT JOIN RB_BusinessTeam rbt WITH (NOLOCK) ON RB_Admin.BusinessTeamID = rbt.ID
          AND RB_Admin.CustomerID = rbt.CustomerID
        WHERE
          ra.CustomerID = @CustomerID
          AND ra.ASINorItemID != ''
        GROUP BY
          ra.CustomerID,
          ra.SKU,
          AdminID,
          ISNULL(UserName, ''),
          BusinessTeamName,
          ra.IsFBA,
          ra.OnTheWayType,
          ra.IsWithSell
      ) a
    GROUP BY
      CustomerID,
      AdminID,
      ISNULL(UserName, ''),
      BusinessTeamName,
      IsFBA,
      OnTheWayType
    ORDER BY
      AdminID,
      IsFBA,
      OnTheWayType
  ) ccc ON aaa.BusinessAdminID = ccc.BusinessAdminID
  AND aaa.CustomerID = ccc.CustomerID
  AND aaa.IsFBA = ccc.IsFBA
  AND aaa.OnTheWayType = ccc.OnTheWayType
  LEFT JOIN (
    SELECT
      CustomerID,
      BusinessAdminID,
      COUNT(0) AS 出单SKU数量,
      IsFBA,
      OnTheWayType
    FROM
      (
        SELECT
          CustomerID,
          BusinessAdminID,
          IsFBA,
          OnTheWayType
        FROM
          #TempAddedSale
        GROUP BY
          CustomerID,
          BusinessAdminID,
          SKU,
          IsFBA,
          OnTheWayType
      ) b
    GROUP BY
      CustomerID,
      BusinessAdminID,
      IsFBA,
      OnTheWayType
  ) ddd ON aaa.BusinessAdminID = ddd.BusinessAdminID
  AND aaa.CustomerID = ddd.CustomerID
  AND aaa.IsFBA = ddd.IsFBA
  AND aaa.OnTheWayType = ddd.OnTheWayType
UNION ALL
SELECT
  '汇总' AS 上架人员,
  '' AS 上架人员销售小组,
  '' AS listing从属销售小组,
  (
    CASE
      aaa.IsFBA
      WHEN 0 THEN '自发货'
      ELSE 'FBA发货'
    END
  ) AS 发货类型,
  '' AS 上架方式,
  SUM(销售数量),
  SUM([销售额(RMB)]),
  SUM([估算利润(RMB)]),
  SUM(上架listing数量),
  SUM(出单listing数量),
  CASE
    WHEN SUM(上架listing数量) != 0 THEN CONVERT(MONEY, SUM(出单listing数量)) / SUM(上架listing数量)
    ELSE 0
  END AS 上架人员listing出单率,
  SUM(上架SKU数量),
  SUM(出单SKU数量),
  CASE
    WHEN SUM(上架SKU数量) != 0 THEN CONVERT(MONEY, SUM(出单SKU数量)) / SUM(上架SKU数量)
    ELSE 0
  END AS 上架人员SKU出单率,
  '-'
FROM
  (
    SELECT
      CustomerID,
      AdminID AS BusinessAdminID,
      SUM(
        CASE
          WHEN IsWithSell = 1 THEN 0
          ELSE 1
        END
      ) AS 上架listing数量,
      ISNULL(UserName, '') AS UserName,
      IsFBA,
      OnTheWayType
    FROM
      (
        SELECT
          ra.CustomerID,
          AdminID,
          ISNULL(UserName, '') AS UserName,
          COUNT(0) AS countNumber,
          ra.IsFBA,
          ra.OnTheWayType,
          ra.IsWithSell
        FROM
          #tabOrderSourceSKU ra (NOLOCK)
          LEFT JOIN RB_Admin ON ra.AdminID = RB_Admin.ID
          AND ra.CustomerID = RB_Admin.CustomerID
          LEFT JOIN RB_OrderSource ro WITH (NOLOCK) ON ra.OrderSourceID = ro.ID
          AND ra.CustomerID = ro.CustomerID
        WHERE
          ra.CustomerID = @CustomerID
          AND ra.ASINorItemID != ''
        GROUP BY
          ra.CustomerID,
          ra.OrderSourceID,
          ra.OrderSourceSKU,
          ra.SKU,
          AdminID,
          ISNULL(UserName, ''),
          IsFBA,
          OnTheWayType,
          ra.IsWithSell
      ) a
    GROUP BY
      CustomerID,
      AdminID,
      ISNULL(UserName, ''),
      IsFBA,
      OnTheWayType
  ) aaa
  LEFT JOIN (
    SELECT
      CustomerID,
      BusinessAdminID,
      SUM(销售数量) AS 销售数量,
      SUM([销售额(RMB)]) AS [销售额(RMB)],
      SUM([估算利润(RMB)]) AS [估算利润(RMB)],
      SUM(最早出单天数) / COUNT(0) AS 平均出单天数,
      COUNT(0) AS 出单listing数量,
      IsFBA,
      OnTheWayType
    FROM
      (
        SELECT
          CustomerID,
          BusinessAdminID,
          SUM(
            CASE
              WHEN GroupSKU <> '' THEN GroupSaleCount
              ELSE SaleCount
            END
          ) AS 销售数量,
          SUM(SalePrice) AS [销售额(RMB)],
          SUM(ProfitPrice) AS [估算利润(RMB)],
          MIN(SingleDays) AS 最早出单天数,
          IsFBA,
          OnTheWayType
        FROM
          #TempAddedSale
        GROUP BY
          CustomerID,
          BusinessAdminID,
          (
            CASE
              WHEN GroupSKU <> '' THEN GroupSKU
              ELSE SKU
            END
          ),
          SellerSKU,
          IsFBA,
          OnTheWayType
      ) b
    GROUP BY
      CustomerID,
      BusinessAdminID,
      IsFBA,
      OnTheWayType
  ) bbb ON aaa.BusinessAdminID = bbb.BusinessAdminID
  AND aaa.CustomerID = bbb.CustomerID
  AND aaa.IsFBA = bbb.IsFBA
  AND aaa.OnTheWayType = bbb.OnTheWayType
  LEFT JOIN (
    SELECT
      TOP 100 PERCENT CustomerID,
      AdminID AS BusinessAdminID,
&n