跨表根据不同的条件更新不同的数据

场景描述

订单数据存放分为主子表存放，订单表：存放订单相关的信息，订单商品表：存放订单商品信息，需要根据销售日期，及商品的SKU号来筛选出对应的订单商品表列数据，然后根据不同的商家来修改订单商品表中的价格

数据模拟

数据表

订单表

列名	字段
order_id	订单号
merchant_id	商家号
sale_date	销售日期

• 建表SQL

CREATE TABLE `order` (
 `id` bigint(20) NOT NULL COMMENT '订单号',
 `merchant_id` bigint(20) NOT NULL COMMENT '商家ID',
 `sale_date` datetime NOT NULL COMMENT '销售日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表'

订单商品表

列名	字段
id	主键
order_id	订单号
sku_id	商品SKU号
price	价格

• 建表SQL

CREATE TABLE `order_item` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) DEFAULT NULL COMMENT '订单号',
  `sku_id` bigint(20) NOT NULL COMMENT 'sku号',
  `price` bigint(20) NOT NULL COMMENT '价格',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='订单商品表'

数据准备

• 订单数据

INSERT INTO `order` (id, merchant_id, sale_date) VALUES (10652967250, 1, '2021-01-20 12:07:34');
INSERT INTO `order` (id, merchant_id, sale_date) VALUES (10820962829, 2, '2021-01-21 15:23:07');
INSERT INTO `order` (id, merchant_id, sale_date) VALUES (11367471723, 3, '2021-01-21 23:52:17');
INSERT INTO `order` (id, merchant_id, sale_date) VALUES (12823806163, 1, '2021-01-21 14:35:56');
INSERT INTO `order` (id, merchant_id, sale_date) VALUES (12837222162, 2, '2021-01-21 09:12:34');

• 订单明细数据

INSERT INTO demo.order_item (id, order_id, sku_id, price) VALUES (1, 10652967250, 100, 256);
INSERT INTO demo.order_item (id, order_id, sku_id, price) VALUES (2, 10820962829, 100, 653);
INSERT INTO demo.order_item (id, order_id, sku_id, price) VALUES (3, 10820962829, 123, 865);
INSERT INTO demo.order_item (id, order_id, sku_id, price) VALUES (4, 11367471723, 100, 542);
INSERT INTO demo.order_item (id, order_id, sku_id, price) VALUES (5, 11367471723, 456, 1234);
INSERT INTO demo.order_item (id, order_id, sku_id, price) VALUES (6, 12823806163, 100, 7892);
INSERT INTO demo.order_item (id, order_id, sku_id, price) VALUES (7, 12823806163, 345, 8653);
INSERT INTO demo.order_item (id, order_id, sku_id, price) VALUES (8, 12837222162, 100, 5832);
INSERT INTO demo.order_item (id, order_id, sku_id, price) VALUES (9, 12837222162, 567, 7458);

修改数据操作

修改场景

将订单销售日期大于2021-01-21，并且SKU_ID=100,根据不同的商家ID修改订单明细的价格

商家ID	修改后价格
1	123
2	256
3	456

查询满足条件的数据

• 子查询

  select  * from  order_item where order_id in (
  select  id from  `order` where  sale_date >='2021-01-21'
  ) and sku_id = 100
  

• 连表查询

  select  oi.* from  `order`
  left join  order_item oi on `order`.id = oi.order_id
  where  `order`.sale_date >='2021-01-21'  and oi.sku_id =100
  

• 多表查询

  select  oi.* from  `order` o,order_item oi
  where  o.sale_date >='2021-01-21' and oi.sku_id = 100 and o.id = oi.order_id
  

修改数据

由于要根据主表的商家ID修改不同的价格，条件判断与修改的数据字段不在同一张表，这就比较难，我用的是多表查询的方法结合case来修改数据

• 方案一

UPDATE  `order` o,order_item oi
set  oi.price = case
    when  o.merchant_id = 1 then  123
    when  o.merchant_id = 2 then  256
    when  o.merchant_id = 3 then  456
    else oi.price
end
where  o.sale_date >='2021-01-21' and oi.sku_id = 100 and o.id = oi.order_id

修改后结果: