更新订单sku

查询产品id

SELECT b.*,c.* from nr_order a,nr_order_product b, nr_order_option c
 WHERE a.id=b.order_id and b.id=c.order_product_id and a.order_number='tw807231743223223';

查询订单详情

select * from nr_order_option where id in(
 SELECT c.id from nr_order a,nr_order_product b, nr_order_option c
 WHERE a.id=b.order_id and b.id=c.order_product_id and a.order_number='订单号'
);

查询运单详情

select * from nr_order_option where id in(
 SELECT c.id from nr_order a,nr_order_product b, nr_order_option c
 WHERE a.id=b.order_id and b.id=c.order_product_id and a.waybill_number='运单号'
);

将产品id为107243，poid为0的产品更新为poid=459632，规格值为84450，205110

#update nr_order_product b, nr_order_option c set c.poid=459632,c.options_value='84450,205110' where b.id=c.order_product_id and b.goods_id=107243 and poid=0

根据产品id查询正确sku

select poid,options_value,`status`,chinese_pice from nr_product_options where status=1 and goods_id='107243';

根据规格值查询正确的poid（运动鞋）

SELECT status,poid,options_value FROM nr_product_options WHERE options_value LIKE '84765,84773%'

查询仓库poid

select * from gwms.nr_stock where poid=469829;

更新订单sku(二)（供参考）

SELECT b.*,c.* from nr_order a,nr_order_product b, nr_order_option c
 WHERE a.id=b.order_id and b.id=c.order_product_id and a.order_number='TW807262029546600';

select * from nr_order_option where id in(
 SELECT c.id from nr_order a,nr_order_product b, nr_order_option c
 WHERE a.id=b.order_id and b.id=c.order_product_id and b.goods_id='121747'
);

#update nr_order_product b, nr_order_option c set c.poid=462873,options_value='190728,206667' where b.id=c.order_product_id and b.goods_id=121747;

-- update nr_order_product b, nr_order_option c, (select goods_id,poid,options_value from nr_product_options where `status`=1 group by goods_id having count(*)=1) d 
-- set c.poid=d.poid,c.options_value=d.options_value where b.id=c.order_product_id and d.goods_id=b.goods_id and c.poid=0;

select * from nr_order_product b, nr_order_option c, (select goods_id,poid,options_value from nr_product_options where `status`=1 group by goods_id having count(*)=1) d 
 where b.id=c.order_product_id and b.goods_id=d.goods_id and c.poid=0;



select * from nr_product_options where goods_id='121747';

select * from gwms.nr_stock where poid=462873;

单规格值SQL

update nr_order_product a, nr_order_option b, (
select goods_id,poid from nr_product_options where status=1 group by goods_id having count(*)=1 
) c set b.poid=c.poid where a.id=b.order_product_id and a.goods_id=c.goods_id  and b.poid=0;

select id from nr_product where goods_id in(
    select goods_id from nr_product_options where status=1 group by goods_id having count(*)=1 
) and status=1

搜索产品对应的订单sku

select * from nr_order_option where id in(
 SELECT c.id from nr_order a,nr_order_product b, nr_order_option c
 WHERE a.id=b.order_id and b.id=c.order_product_id and b.goods_id='产品ID'
);