需求,应用场景
table1是统计信息表，里面存储了商店id,一个商店一条数据，table2是订单表，里面存储了多个订单，每条订单有一个字段是table1的商店id，table3是商品表，存储了多个商品，table2里面的每条数据在table3里面有1-N条商品数据，table1.shop_id=table2.shop_id，table2.order_id=table3.order_id,把table3同一个商店下面的商品数量统计一下，写入到table1表的销量统计字段里
mysql语句
update table1 t1, //修改t1表 (select sum(t3.num) as s , t2.shop_id as shop_id,t2.order_status as order_status FROM table2 as t2 join table3 as t3 on t2.order_id = t3.order_id WHERE t2.order_status = 4 ) c //连表查询t2,t3查询结果别名c set t1.sales_volume = c.s //t1.sales_volume = c.s 字段值 WHERE t1.shop_id = c.shop_id and c.order_status = 4 //t1.shop_id = 查询结果集的shop_id