select top 100 a.OrderSourceName '来源渠道'
,a.SKU '系统SKU'
,a.OrderSourceSKU '渠道SKU'
,a.RecommendedBrowseNodeName '上架类目路径'
,(select top 1 d.PathName from RB_Amazon_Product_KeyClassResult d (nolock) where d.CustomerID=a.CustomerID and d.SKU= a.SKU order by d.SerialNo asc ) '推荐类目路径'
,a.BusinessTeamName '销售小组'
,a.BusinessUserName '销售人员'
,a.AddAdminName '上架人员'
,c.BusinessTeamName '上架小组'
,a.PublishTime '刊登时间'
,a.msaleNum '近30天销量'
,case when a.OnShelfMode=1 then '一键上架'
when a.OnShelfMode=2 then '草稿上架'
when a.OnShelfMode=3 then '表格上架'
when a.OnShelfMode=4 then '关联导入'
when a.OnShelfMode=5 then '批量跟卖'
when a.OnShelfMode=6 then '凤凰重刊'
when a.OnShelfMode=7 then '复制上架'
when a.OnShelfMode=8 then '智能刊登'
else '' end as '上架方式',
case when a.IsOffShelf=0 then '在线' else '下架' end '在线状态'
from RB_Amazon_Product_Base a (nolock)
left join RB_Admin b (nolock) on a.CustomerID=b.CustomerID and a.AddAdminID = b.ID
left join RB_BusinessTeam c (nolock) on c.CustomerID = b.CustomerID and c.id = b.BusinessTeamID
where a.CustomerID=1 and a.OrderSourceSKUID >1
and a.PublishTime >='2022-01-01' and a.PublishTime <'2022-05-01'
任务描述
需求描述
1.导出范围
刊登时间：2022年1月1日-2022年4月30日；listing维度，系统推荐类目与实际上传类目是否匹配明细
2.导出字段
来源渠道，系统SKU，渠道SKU，上架类目路径，推荐类目路径，销售小组，销售人员，上架人员，上架小组，刊登时间，近30天销量，上架方式
验收标准
暂无
数据已导出，修改后导出sql，如下：
select
OrderSourceName '来源渠道',
SKU '系统SKU',
OrderSourceSKU '渠道SKU',
RecommendedBrowseNodeName '上架类目路径',
LastRecommendedBrowseNodeName '推荐类目路径',
BusinessTeamName '销售小组',
BusinessUserName '销售人员',
AddAdminName '上架人员',
offBusinessTeamName '上架小组',
PublishTime '刊登时间',
msaleNum '近30天销量',
case when OnShelfMode=1 then '一键上架'
when OnShelfMode=2 then '草稿上架'
when OnShelfMode=3 then '表格上架'
when OnShelfMode=4 then '关联导入'
when OnShelfMode=5 then '批量跟卖'
when OnShelfMode=6 then '凤凰重刊'
when OnShelfMode=7 then '复制上架'
when OnShelfMode=8 then '智能刊登'
else '' end as '上架方式',
CASE
WHEN IsOffShelf = 0 THEN '在线'
WHEN IsOffShelf = 1 THEN '下架'
WHEN IsOffShelf = 2 THEN '售完'
WHEN IsOffShelf = 3 THEN '草稿'
WHEN IsOffShelf = 4 THEN '刊登中'
WHEN IsOffShelf = 5 THEN '刊登失败'
ELSE ''
END '在线状态'
from(
SELECT
ROW_NUMBER() OVER (ORDER BY ID asc) AS [PageIndex] ,* FROM (
select a.OrderSourceName
,a.SKU
,a.OrderSourceSKU
,a.RecommendedBrowseNodeName
,(select top 1 d.PathName from RB_Amazon_Product_KeyClassResult d (nolock) where d.CustomerID=a.CustomerID and d.SKU= a.SKU order by d.SerialNo asc ) as LastRecommendedBrowseNodeName
,a.BusinessTeamName
,a.BusinessUserName
,a.AddAdminName
,c.BusinessTeamName as offBusinessTeamName
,a.PublishTime
,a.msaleNum
,a.OnShelfMode
,a.IsOffShelf
,a.ID
from RB_Amazon_Product_Base a (nolock)
inner join RB_OrderSource d(nolock)
on a.CustomerID=d.CustomerID
and a.OrderSourceID=d.ID
left join RB_Admin b (nolock) on a.CustomerID=b.CustomerID and a.AddAdminID = b.ID
left join RB_BusinessTeam c (nolock) on c.CustomerID = b.CustomerID and c.id = b.BusinessTeamID
where a.CustomerID=1 and a.OrderSourceSKUID >0 and a.OnShelfMode in(1,2,7,8)
and d.OrderSourceCountry='US'
and a.PublishTime >='2022-01-01' and a.PublishTime <'2022-05-01'
) AS TempTable ) AS PagerTable
WHERE PagerTable.[PageIndex] BETWEEN 1 AND 600000