深圳智汇创想科技有限公司

任务背景：

当前lazada 和kogan 的新品，因为缺失类目问题，导致无法刊登，故现在需要按照之前的刷新逻辑，进行一次数据刷新，保证未切换白名单的账号可以正常获取到类目刊登

任务内容：

lazada 站点类目数据刷新SQL，执行已下逻辑

drop table temp_table;
create temporary table temp_table(
  sku varchar(16),
	SiteCode varchar(16),
	CategoryId varchar(256),
	`Name` varchar(128),
	ParentId varchar(128),
	FirstCategoryId varchar(128),
	CombinationNameEn varchar(1024),
	KEY pk(name)
) ENGINE = InnoDB;

INSERT into temp_table
SELECT a.Sku,a.SiteCode,a.CategoryId,b.`Name`,b.ParentId,b.FirstCategoryId,b.CombinationNameEn from p_market_product a
left join m_productcategory b on a.MarketId = b.MarketId and a.CategoryId = b.CategoryId and b.SiteCode = 'MY'
-- left join m_productcategory c on a.MarketId = c.MarketId and b.`Name` = c.`Name` and b.SiteCode = 'ID'
-- left join m_productcategory d on a.MarketId = d.MarketId and b.`Name` = d.`Name` and b.SiteCode = 'PH'
-- left join m_productcategory e on a.MarketId = e.MarketId and b.`Name` = e.`Name` and b.SiteCode = 'SG'
-- left join m_productcategory f on a.MarketId = f.MarketId and b.`Name` = f.`Name` and b.SiteCode = 'TH'
-- left join m_productcategory g on a.MarketId = g.MarketId and b.`Name` = g.`Name` and b.SiteCode = 'VN'
WHERE a.CustomerId=1 AND a.MarketId=58  and a.SiteCode = 'MY' and a.CategoryId is not null and a.CategoryId <> '' and a.LastModificationTime > '2023-02-07';


  update p_market_product m
 -- select m.sku,m.sitecode,m.CategoryId,c.sku,c.sitecode,c.CategoryId from p_market_product m
left join (
select a.sku,'PH' SiteCode,b.CategoryId from temp_table a
left join (select * from m_productcategory where MarketId = 58  and IsLeaf = 1 and SiteCode = 'PH') b on a.`Name` = b.`Name` and a.CombinationNameEn = b.CombinationNameEn
where b.CategoryId is not null 
) c on m.sku = c.sku and m.SiteCode = c.SiteCode
 set m.CategoryId = c.CategoryId,m.LastModifier = '0310类目更新'
where  m.CustomerId=1 AND m.MarketId=58 and m.SiteCode = 'PH' and (m.CategoryId is null or m.CategoryId = '') and c.sku is not null;

  update p_market_product m
 -- select m.sku,m.sitecode,m.CategoryId,c.sku,c.sitecode,c.CategoryId from p_market_product m
left join (
select a.sku,'SG' SiteCode,b.CategoryId from temp_table a
left join (select * from m_productcategory where MarketId = 58  and IsLeaf = 1 and SiteCode = 'SG') b on a.`Name` = b.`Name` and a.CombinationNameEn = b.CombinationNameEn
where b.CategoryId is not null 
) c on m.sku = c.sku and m.SiteCode = c.SiteCode
 set m.CategoryId = c.CategoryId,m.LastModifier = '0310类目更新'
where  m.CustomerId=1 AND m.MarketId=58 and m.SiteCode = 'SG' and (m.CategoryId is null or m.CategoryId = '') and c.sku is not null;

  update p_market_product m
 -- select m.sku,m.sitecode,m.CategoryId,c.sku,c.sitecode,c.CategoryId from p_market_product m
left join (
select a.sku,'TH' SiteCode,b.CategoryId from temp_table a
left join (select * from m_productcategory where MarketId = 58  and IsLeaf = 1 and SiteCode = 'TH') b on a.`Name` = b.`Name` and a.CombinationNameEn = b.CombinationNameEn
where b.CategoryId is not null 
) c on m.sku = c.sku and m.SiteCode = c.SiteCode
 set m.CategoryId = c.CategoryId,m.LastModifier = '0310类目更新'
where  m.CustomerId=1 AND m.MarketId=58 and m.SiteCode = 'TH' and (m.CategoryId is null or m.CategoryId = '') and c.sku is not null;

  update p_market_product m
 -- select m.sku,m.sitecode,m.CategoryId,c.sku,c.sitecode,c.CategoryId from p_market_product m
left join (
select a.sku,'VN' SiteCode,b.CategoryId from temp_table a
left join (select * from m_productcategory where MarketId = 58  and IsLeaf = 1 and SiteCode = 'VN') b on a.`Name` = b.`Name` and a.CombinationNameEn = b.CombinationNameEn
where b.CategoryId is not null 
) c on m.sku = c.sku and m.SiteCode = c.SiteCode
 set m.CategoryId = c.CategoryId,m.LastModifier = '0310类目更新'
where  m.CustomerId=1 AND m.MarketId=58 and m.SiteCode = 'VN' and (m.CategoryId is null or m.CategoryId = '') and c.sku is not null

kogan 类目刷新规则

按照业务提供的表格，进行数据刷新

将类目刷新到P_market_product表&

并在p_market_product_review中，将SKU刷新为审核通过

LastModifier记录：3月10日类目手动更新