编辑 | blame | 历史 | 原始文档

需求:
1、所有厂家是国药的试剂,供应商为国药试剂;
2、 如果是管制品,厂家不是国药是的试剂,供应商为福得科技;
3、 其他所有试剂厂家为思锐达

select id from base_meta where group_id='product_name' and meta_value='国药试剂'

select id from sys_supplier where name="国药集团化学试剂苏州有限公司"

select id from sys_supplier where name="苏州福得科技有限公司"

select id from sys_supplier where name="上海思锐达化学科技有限公司"

select * from sys_reagent a
inner join base_meta b on a.product_home=b.id
where b.group_id='product_name' and b.meta_value='国药试剂' and a.type=1

select * from sys_reagent a
where a.product_home not in (select id from base_meta where group_id='product_name' and meta_value='国药试剂')
and (a.control_products is not null and a.control_products!='') and a.type=1

select * from sys_reagent a
where a.product_home not in (select id from base_meta where group_id='product_name' and meta_value='国药试剂')
and (a.control_products is null or a.control_products='') and a.type=1

select a.* from sys_reagent a
left join base_meta b on b.id=a.control_products
where a.product_home not in (select id from base_meta where group_id='product_name' and meta_value='国药试剂')
and (b.meta_value='' or b.meta_value is null) and a.type=1

update sys_reagent set supplier_id =(select id from sys_supplier where name="国药集团化学试剂苏州有限公司")
where id in (select bb.id from (select a.id from sys_reagent a
inner join base_meta b on a.product_home=b.id
where b.group_id='product_name' and b.meta_value='国药试剂' and a.type=1) as bb)

update sys_reagent set supplier_id =(select id from sys_supplier where name="苏州福得科技有限公司")
where id in (select bb.id from (select a.id from sys_reagent a
left join base_meta b on b.id=a.control_products
where a.product_home not in (select id from base_meta where group_id='product_name' and meta_value='国药试剂')
and (a.control_products is not null and a.control_products!='') and a.type=1
and b.meta_value!='' and b.meta_value is not null) as bb)

update sys_reagent set supplier_id =(select id from sys_supplier where name="上海思锐达化学科技有限公司" limit 1)
where id in (select bb.id from (select a.id from sys_reagent a
left join base_meta b on b.id=a.control_products
where a.product_home not in (select id from base_meta where group_id='product_name' and meta_value='国药试剂')
and (b.meta_value='' or b.meta_value is null) and a.type=1) as bb)