北药产品规整sql

作者: yichen_china | 来源:发表于2021-11-23 19:10 被阅读0次

北药产品规整sql
Pnadas 通过SQL处理数据并读取结果到DF
MySQL干货！21个写SQL的好习惯
规整
MySQL中，21个写SQL的好习惯
Mysql中，21个写SQL的好习惯，你值得拥有呀
Mysql查询常用总结.值得收藏.
产品规划以及产品设计
五大连池世界名园——有趣的南药泉
每月报表总结

medicine_info_22w 产品库合入 zt_product_base
说明书合入基础库

update  v_sm_product_info a,medicine_info_22w b SET a.instruction_book=b.medicine_instruction WHERE  a.bar_code=b.medicine_barcode  AND LENGTH(b.medicine_instruction)>10

没主图有更多图的截取第一个图设为主图

UPDATE zt_product_base SET first_image =LEFT(images,INSTR(images,",")-1)  WHERE !ISNULL(goods_no)  AND ISNULL(brand_name)  AND ISNULL(first_image) and !ISNULL(images)

适应症合入基础库

update  v_sm_product_info a,medicine_info_22w b SET a.indications=b.medicine_indication WHERE  a.bar_code=b.medicine_barcode  AND  LENGTH(b.medicine_indication)>10 and (LENGTH(a.indications)<10 or  a.indications is null)

成分合入基础库

update  v_sm_product_info a,medicine_info_22w b SET a.main_ingredients=b.medicine_component WHERE  a.bar_code=b.medicine_barcode  AND LENGTH(b.medicine_component)>10 and (LENGTH(a.main_ingredients)<10 or  a.main_ingredients is null)

#SELECT COUNT(a.id) FROM zt_product_base a, medicine_info_22w b WHERE b.medicine_barcode  LIKE  CONCAT("%",a.upc,"%")
#SELECT COUNT(a.id) FROM zt_product_base a, medicine_info_22w b WHERE b.medicine_barcode  = a.upc and a.instruction_book is null and b.medicine_instruction is not null
根据条形码 更新说明书字段
#UPDATE zt_product_base a, medicine_info_22w b SET a.instruction_book = b.medicine_instruction WHERE b.medicine_barcode  = a.upc and (LENGTH(a.instruction_book)<10 or a.instruction_book is null) and b.medicine_instruction is not null

品牌名如果不在说明书里存在更新为说明书的品牌名

SELECT id,brand_name,regexp_replace(REPLACE(REPLACE(REPLACE(substring_index(substring_index(instruction_book ,"【商品名/商标】",-1),"【",1), '　',""),"
","")," ",""),'<.+?>','') as brand_name2,regexp_replace(instruction_book ,'<[^/|a-z|A-Z].?>',''),instruction_book FROM zt_product_base WHERE instruction_book like "%【商品名/商标】%"  and brand_name!= regexp_replace(REPLACE(REPLACE(REPLACE(substring_index(substring_index(instruction_book ,"【商品名/商标】",-1),"【",1), '　',""),"
","")," ",""),'<.+?>','')  and   instruction_book not like CONCAT("%",brand_name,"%")  LIMIT 100

UPDATE zt_product_base set brand_name= regexp_replace(REPLACE(REPLACE(REPLACE(substring_index(substring_index(instruction_book ,"【商品名/商标】",-1),"【",1), '　',""),"
","")," ",""),'<.+?>','')  WHERE  instruction_book like "%【商品名/商标】%"  and brand_name!= regexp_replace(REPLACE(REPLACE(REPLACE(substring_index(substring_index(instruction_book ,"【商品名/商标】",-1),"【",1), '　',""),"
","")," ",""),'<.+?>','')  and   instruction_book not like CONCAT("%",brand_name,"%")

如果说明书格式不含【商品名/商标】处理

SELECT id,brand_name,regexp_replace(REPLACE(REPLACE(REPLACE(substring_index(substring_index(instruction_book ,"商品名/商标",-1),"<",3), '　',""),"
","")," ",""),'<.+?>','') as brand_name2,regexp_replace(instruction_book ,'<[^/|a-z|A-Z].?>',''),instruction_book FROM zt_product_base WHERE instruction_book like "%商品名/商标%" and  instruction_book  not like "%【商品名/商标】%"  and brand_name!= regexp_replace(REPLACE(REPLACE(REPLACE(substring_index(substring_index(instruction_book ,"商品名/商标",-1),"<",3), '　',""),"
","")," ",""),'<.+?>','')  and   instruction_book not like CONCAT("%",brand_name,"%")  LIMIT 100


UPDATE zt_product_base set brand_name= regexp_replace(REPLACE(REPLACE(REPLACE(substring_index(substring_index(instruction_book ,"商品名/商标",-1),"<",3), '　',""),"
","")," ",""),'<.+?>','')  WHERE  instruction_book like "%商品名/商标%"  and brand_name!= regexp_replace(REPLACE(REPLACE(REPLACE(substring_index(substring_index(instruction_book ,"商品名/商标",-1),"<",3), '　',""),"
","")," ",""),'<.+?>','')  and   instruction_book not like CONCAT("%",brand_name,"%")  and  instruction_book  not like "%【商品名/商标】%"

商贸产品信息写入中台产品信息字段对照
商贸新增产品信息，中台不存在的货号会导入，中台存在的不会导入

-- SELECT product_id,if(bar_code="",null,bar_code),product_name,common_name,pinyin,if(images="",null,images),spec,factory_name,producing_area,if
-- (approval_no="",null,approval_no),dosage,gsp_type,effect,quality_standard,unit,packaging_quantity,medium_packaging_quantity,packing_unit,prescription_type,validity_date,whole_package_height,whole_package_height,volume,weight,storage_condition,license_legal_person,drug_supervision_no,split_type,imported_type,cold_storage_type,external,injection,is_insulin,special_type FROM sm_product_info LIMIT 10

INSERT into zt_product_base(goods_no,upc,product_name,common_name,pinyin,images,spec,manufacture,producing_area,approval_number,dosage_form,gsp_type,indications,quality_standard,unit,packaging_quantity,medium_packaging_quantity,packing_unit,prescription_type,indate,whole_package_length,whole_package_width,whole_package_height,volume,weight,storage_condition,license_legal_person,drug_supervision_no,split_type,imported_type,cold_storage_type,external,injection,is_insulin,special_type)  SELECT d.product_id,if(d.bar_code="",null,d.bar_code) bar_code,d.product_name,d.common_name,d.pinyin,if(d.images="",null,d.images) images,d.spec,d.factory_name,d.producing_area,if
(d.approval_no="",null,d.approval_no) approval_no,d.dosage,d.gsp_type,d.effect,d.quality_standard,d.unit,d.packaging_quantity,d.medium_packaging_quantity,d.packing_unit,d.prescription_type,d.validity_date,d.whole_package_length,d.whole_package_width,d.whole_package_height,d.volume,d.weight,d.storage_condition,d.license_legal_person,d.drug_supervision_no,d.split_type,d.imported_type,d.cold_storage_type,d.external,d.injection,d.is_insulin,d.special_type FROM sm_product_info d left join zt_product_base e on(d.product_id=e.goods_no)  WHERE e.goods_no is null

查出产品库有库存，没图片没品牌的数据

SELECT b.id,s.stock 库存,s.product_id 货号,brand_name 品牌,first_image 主图,images 多图逗号分割 FROM `zt_product_base` b,sm_stock s WHERE b.goods_no=s.product_id  and !ISNULL(b.goods_no)  AND ISNULL(b.brand_name)  AND ISNULL(b.first_image)  and s.stock> 0

查询出产品库没图并且ERP有图的

SELECT  s.product_id, LEFT(s.images,INSTR(s.images,",")-1) imgs,s.images,s.product_name FROM zt_product_base z LEFT JOIN `sm_product_info` s on s.product_id =z.goods_no  WHERE (s.images !="" and LENGTH(s.images) > 6) and z.first_image is null