更新订单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'
);