1、清除一个UPC对应多分类的数据 2.5工时
清理SQL如下：
# -- 清除UPC相同但分类不同的记录 -------- 开始 --------------------
#1.找出UPC相同的记录
insert into tb_allegroupc_tmp (SKu)
select Upc from m_market_allegro_upc
GROUP BY upc
HAVING COUNT(1)>1;
create table m_market_allegro_upc_0920 like m_market_allegro_upc;
insert into m_market_allegro_upc_0920 (Id,Sku,Upc,CategoryId)
select * from m_market_allegro_upc where upc in (
select sku from tb_allegroupc_tmp
);
#2、在1步骤中找到UPC相同且有不同分类的UPC
TRUNCATE TABLE tb_allegroupc_tmp; -- 清空临时表
insert into tb_allegroupc_tmp (SKu)
select tt.upc from (
select upc, Max(CategoryId) maxcateid, Min(CategoryId) as mincateid from m_market_allegro_upc_0920
GROUP BY upc
) as tt where tt.maxcateid<>tt.mincateid
create table m_market_allegro_upc_0920_02 like m_market_allegro_upc;
insert into m_market_allegro_upc_0920_02 (Id,Sku,Upc,CategoryId)
select Id,Sku,Upc,CategoryId from m_market_allegro_upc_0920 where upc in (
select sku from tb_allegroupc_tmp
);
#3.删除UPC相同但分类不同的记录
TRUNCATE TABLE bigid_temp3;
INSERT INTO bigid_temp3 (Id)
select id from m_market_allegro_upc_0920_02 limit 100000;
delete from m_market_allegro_upc_0920_02 where id in (select ID from bigid_temp3);
delete from m_market_allegro_upc where id in (select ID from bigid_temp3);