参考SQL:
SELECT
t2.OrderSourceName '订单来源渠道',t2.OrderSourceCountry '市场',
t3.DepartmentName '部门', bt.BusinessTeamName '小组',t2.SaleAdminName '销售',vtb.OrderSourceSKU '渠道SKU', tt.ASIN 'ASIN'
, case
vtb.OnShelfMode
when 1 then '一键上架'
when 2 then '草稿上架'
when 3 then '表格上架'
when 4 then '关联导入'
when 5 then '批量跟卖'
when 6 then '凤凰重刊'
end '刊登方式',
vtb.AddTime 'listing刊登时间',
t1.AddTime '首次备货时间',
t1.OrderCode '备库订单号'
FROM (
SELECT os.OrderSourceCountry,TRIM(Replace(Replace(Replace(ol.ASIN,'\t',''),'\n',''),'\r','')) AS ASIN,MIN(oi.ID) oiId,min(vtba.OrderSourceSKUID) OrderSourceSKUID
FROM dbo.RB_OrderInfo oi (NOLOCK)
INNER JOIN RB_OrderInfo_BeikuExpand ep (NOLOCK)
ON oi.CustomerID = ep.CustomerID
AND oi.OrderCode = ep.OrderCode
INNER JOIN dbo.RB_OrderList ol (nolock) ON ol.CustomerID=oi.CustomerID
AND ol.OrderCode=oi.OrderCode
LEFT OUTER JOIN RB_OrderSource (NOLOCK) os
ON oi.OrderSourceID = os.ID
AND oi.CustomerID = os.CustomerID
LEFT OUTER JOIN RB_Transport (NOLOCK) ts
ON oi.TransportID = ts.ID
AND oi.CustomerID = ts.CustomerID
LEFT JOIN dbo.RB_WareHouse (NOLOCK) wh
ON oi.CustomerID = wh.CustomerID
AND oi.WareHouseID = wh.ID
LEFT JOIN dbo.RB_WareHouse (NOLOCK) towh
ON oi.CustomerID = towh.CustomerID
AND oi.ToWareHouseID = towh.ID
LEFT JOIN RB_OrderInfo_Beiku (NOLOCK) beiku
ON beiku.CustomerID = oi.CustomerID
AND beiku.OrderCode = oi.OrderCode
LEFT JOIN RB_OrderInfo_BeikuExpand (NOLOCK) epo
ON epo.TransitOrderCode = oi.OrderCode
AND epo.CustomerID = oi.CustomerID
LEFT JOIN RB_RestockOrder_ApprovalExecutor (NOLOCK) ae
ON ae.CustomerId = oi.CustomerID
AND ae.OrderCode = oi.OrderCode
INNER JOIN RB_FBA_ShipmentInfo (NOLOCK) fbasi
ON oi.CustomerID = fbasi.CustomerID
AND fbasi.OrderSourceID = oi.OrderSourceID
AND oi.AmazonShipmentId = fbasi.ShipmentId
INNER JOIN dbo.RB_Amazon_Product_Base vtba (NOLOCK) ON vtba.CustomerID=oi.CustomerID
AND vtba.OrderSourceID =oi.OrderSourceID AND vtba.OrderSourceSKU = ol.SellerSKU
WHERE oi.CustomerID = 1
AND oi.IsStockOrder = 1
AND os.OrderSourceType = 1
AND towh.WareHouseType = 3
AND ep.OrderBuildType = 1
AND oi.IsSystem = 0
GROUP BY os.OrderSourceCountry,TRIM(Replace(Replace(Replace(ol.ASIN,'\t',''),'\n',''),'\r','')) )TT
INNER JOIN RB_OrderInfo t1 (nolock) ON t1.ID=tt.oiId
INNER JOIN dbo.RB_Amazon_Product_Base vtb (NOLOCK) ON vtb.OrderSourceSKUID=TT.OrderSourceSKUID
INNER JOIN dbo.RB_OrderSource t2 (nolock) ON t2.ID=vtb.OrderSourceID
LEFT JOIN RB_Admin rba WITH (NOLOCK)
ON rba.CustomerID = t2.CustomerID
AND rba.ID = t2.SaleAdminID
LEFT JOIN RB_BusinessTeam bt WITH (NOLOCK)
ON bt.CustomerID = t2.CustomerID
AND bt.ID = rba.BusinessTeamID
LEFT JOIN dbo.RB_Department t3 (nolock) ON t3.ID=rba.DepartmentID
WHERE vtb.OrderSourceSKUID>0
SELECT
t2.OrderSourceName '订单来源渠道',t2.OrderSourceCountry '市场',
t3.DepartmentName '部门', bt.BusinessTeamName '小组',t2.SaleAdminName '销售',vtb.OrderSourceSKU '渠道SKU', tt.ASIN 'ASIN'
,rp.sku '系统SKU' ,case
rp.DevelopType
when 1 then '自主开发'
when 2 then '供应商后台'
when 3 then '指定开发'
when 4 then '智能选品'
when 5 then '销售提报_FBA新品'
end 'SKU开发类型'
,rpa.CheckTime 'SKU终审时间'
,rp.ProductNameCN '中文名称'
,rpb.BrandName '品牌名称'
,t1.OrderCode '首次到货FBA备库单号'
,TT.minAddTime '首次到货时间'
,(SELECT TOP 1 rsd.QuantityReceived FROM RB_FBA_ShipmentDetail rsd (nolock) WHERE rsd.CustomerID=1 AND rsd.ShipmentId =rsi.ShipmentId AND rsd.OrderSourceID = vtb.OrderSourceID
AND rsd.SellerSKU = vtb.OrderSourceSKU ORDER BY rsd.AddTime ASC ) '首次到货数量'
,vtb.OrderSourceSkuOrderTime '渠道首单时间'
,vtb.AvgDailySales '估算销量'
,(SELECT SUM(ros.ProductNum+ros.ProdutNumUpdate) FROM RB_Report_OrderSettlement ros (nolock) WHERE ros.CustomerID =vtb.CustomerID AND ros.OrderSourceID = vtb.OrderSourceID
AND ros.SellerSKU = vtb.OrderSourceSKU AND datediff(day,ISNULL(TT.minAddTime,'2025-01-01') ,ros.PayDate)>0 AND
datediff(day,ISNULL(TT.minAddTime,'2025-01-01') ,ros.PayDate)<31) '到货时间顺延30天销量'
,(SELECT SUM(SalesPrice) FROM RB_Report_OrderSettlement ros (nolock) WHERE ros.CustomerID =vtb.CustomerID AND ros.OrderSourceID = vtb.OrderSourceID
AND ros.SellerSKU = vtb.OrderSourceSKU AND datediff(day,ISNULL(TT.minAddTime,'2025-01-01') ,ros.PayDate)>0 AND
datediff(day,ISNULL(TT.minAddTime,'2025-01-01') ,ros.PayDate)<31) '到货时间顺延30天销售额'
,(SELECT SUM(Profits) FROM RB_Report_OrderSettlement ros (nolock) WHERE ros.CustomerID =vtb.CustomerID AND ros.OrderSourceID = vtb.OrderSourceID
AND ros.SellerSKU = vtb.OrderSourceSKU AND datediff(day,ISNULL(TT.minAddTime,'2025-01-01') ,ros.PayDate)>0 AND
datediff(day,ISNULL(TT.minAddTime,'2025-01-01') ,ros.PayDate)<31) '到货时间顺延30天利润额'
,(SELECT SUM(ros.ProductNum+ros.ProdutNumUpdate) FROM RB_Report_OrderSettlement ros (nolock) WHERE ros.CustomerID =vtb.CustomerID AND ros.OrderSourceID = vtb.OrderSourceID
AND ros.SellerSKU = vtb.OrderSourceSKU) '累计销量'
,(SELECT SUM(SalesPrice) FROM RB_Report_OrderSettlement ros (nolock) WHERE ros.CustomerID =vtb.CustomerID AND ros.OrderSourceID = vtb.OrderSourceID
AND ros.SellerSKU = vtb.OrderSourceSKU) '累计销售额'
,(SELECT SUM(Profits) FROM RB_Report_OrderSettlement ros (nolock) WHERE ros.CustomerID =vtb.CustomerID AND ros.OrderSourceID = vtb.OrderSourceID
AND ros.SellerSKU = vtb.OrderSourceSKU) '累计毛利润'
, case
vtb.OnShelfMode
when 1 then '一键上架'
when 2 then '草稿上架'
when 3 then '表格上架'
when 4 then '关联导入'
when 5 then '批量跟卖'
when 6 then '凤凰重刊'
end '刊登方式',
vtb.AddTime 'listing刊登时间',
t1.AddTime '首次备货时间',
t1.OrderCode '备库订单号'
FROM (
SELECT os.OrderSourceCountry,TRIM(Replace(Replace(Replace(ol.ASIN,'\t',''),'\n',''),'\r','')) AS ASIN,MIN(oi.ID) oiId,min(vtba.OrderSourceSKUID) OrderSourceSKUID,
MIN(fbasi.ID) minShipmentInfoId,MIN(ttfsd.AddTime) minAddTime
FROM dbo.RB_OrderInfo oi (NOLOCK)
INNER JOIN RB_OrderInfo_BeikuExpand ep (NOLOCK)
ON oi.CustomerID = ep.CustomerID
AND oi.OrderCode = ep.OrderCode
INNER JOIN dbo.RB_OrderList ol (nolock) ON ol.CustomerID=oi.CustomerID
AND ol.OrderCode=oi.OrderCode
LEFT OUTER JOIN RB_OrderSource (NOLOCK) os
ON oi.OrderSourceID = os.ID
AND oi.CustomerID = os.CustomerID
LEFT OUTER JOIN RB_Transport (NOLOCK) ts
ON oi.TransportID = ts.ID
AND oi.CustomerID = ts.CustomerID
LEFT JOIN dbo.RB_WareHouse (NOLOCK) wh
ON oi.CustomerID = wh.CustomerID
AND oi.WareHouseID = wh.ID
LEFT JOIN dbo.RB_WareHouse (NOLOCK) towh
ON oi.CustomerID = towh.CustomerID
AND oi.ToWareHouseID = towh.ID
LEFT JOIN RB_OrderInfo_Beiku (NOLOCK) beiku
ON beiku.CustomerID = oi.CustomerID
AND beiku.OrderCode = oi.OrderCode
LEFT JOIN RB_OrderInfo_BeikuExpand (NOLOCK) epo
ON epo.TransitOrderCode = oi.OrderCode
AND epo.CustomerID = oi.CustomerID
LEFT JOIN RB_RestockOrder_ApprovalExecutor (NOLOCK) ae
ON ae.CustomerId = oi.CustomerID
AND ae.OrderCode = oi.OrderCode
INNER JOIN RB_FBA_ShipmentInfo (NOLOCK) fbasi
ON oi.CustomerID = fbasi.CustomerID
AND fbasi.OrderSourceID = oi.OrderSourceID
AND oi.AmazonShipmentId = fbasi.ShipmentId
INNER JOIN dbo.RB_Amazon_Product_Base vtba (NOLOCK) ON vtba.CustomerID=oi.CustomerID
AND vtba.OrderSourceID =oi.OrderSourceID AND vtba.OrderSourceSKU = ol.SellerSKU
LEFT JOIN RB_FBA_ShipmentDetail ttfsd (NOLOCK) ON ttfsd.CustomerID = 1 AND ttfsd.OrderSourceID = vtba.OrderSourceID
AND ttfsd.ShipmentId = fbasi.ShipmentId AND ttfsd.SellerSKU = vtba.OrderSourceSKU
WHERE oi.CustomerID = 1
AND oi.IsStockOrder = 1
AND os.OrderSourceType = 1
AND towh.WareHouseType = 3
AND ep.OrderBuildType = 1
AND oi.IsSystem = 0
GROUP BY os.OrderSourceCountry,TRIM(Replace(Replace(Replace(ol.ASIN,'\t',''),'\n',''),'\r','')) )TT
INNER JOIN RB_OrderInfo t1 (nolock) ON t1.ID=tt.oiId
INNER JOIN dbo.RB_Amazon_Product_Base vtb (NOLOCK) ON vtb.OrderSourceSKUID=TT.OrderSourceSKUID
INNER JOIN dbo.RB_OrderSource t2 (nolock) ON t2.ID=vtb.OrderSourceID
LEFT JOIN RB_Admin rba WITH (NOLOCK)
ON rba.CustomerID = t2.CustomerID
AND rba.ID = t2.SaleAdminID
LEFT JOIN RB_BusinessTeam bt WITH (NOLOCK)
ON bt.CustomerID = t2.CustomerID
AND bt.ID = rba.BusinessTeamID
LEFT JOIN dbo.RB_Department t3 (nolock) ON t3.ID=rba.DepartmentID
LEFT JOIN dbo.RB_Product rp(nolock) ON rp.CustomerID = vtb.CustomerID AND rp.SKU = vtb.SKU
LEFT JOIN dbo.RB_Product_Admin rpa (nolock) ON rpa.CustomerID = rp.CustomerID
AND rpa.SKU=rp.SKU
LEFT JOIN dbo.RB_Product_Brand rpb (nolock) ON rpb.CustomerID=rp.CustomerID AND rpb.ID = rp.BrandID
LEFT JOIN RB_FBA_ShipmentInfo rsi (nolock) ON rsi.ID=tt.minShipmentInfoId AND rsi.CustomerID = 1
WHERE vtb.OrderSourceSKUID>0 AND vtb.CustomerID = 1