ABC三表关联，将C表数据赋值给A表

目录

• 创建模拟表
• 业务需求
• SQL实现

创建模拟表

表关系
a.id=b.a_id (one->one)
b.id=c.b_id (one->more)
b表相当于AC表关系的纽带

create table a
(
    id      varchar(255) null,
    time    date         null,
    country varchar(255) null,
    bu      varchar(255) null
);
INSERT INTO a (id, time, country, bu) VALUES ('a1', null, 'JP', 'DCG');
INSERT INTO .a (id, time, country, bu) VALUES ('a2', null, 'JP', 'DCG');
INSERT INTO a (id, time, country, bu) VALUES ('a3', null, 'JP', 'DCG');

create table b
(
    id   varchar(255) null,
    a_id varchar(255) null
);
INSERT INTO b (id, a_id) VALUES ('b1', 'a1');
INSERT INTO b (id, a_id) VALUES ('b2', 'a2');

create table c
(
    id     varchar(255) null,
    b_id   varchar(255) null,
    time   date         null,
    status varchar(255) null
);
INSERT INTO c (id, b_id, time, status) VALUES ('c1', 'b1', '2020-11-01', 'Approved');
INSERT INTO c (id, b_id, time, status) VALUES ('c2', 'b1', '2020-11-03', null);
INSERT INTO c (id, b_id, time, status) VALUES ('c3', 'b2', '2020-11-01', 'Approved');
INSERT INTO c (id, b_id, time, status) VALUES ('c4', 'b2', '2020-11-04', '');

业务需求

A表关联的C表数据，只有一条数据是c.status=‘Approved’
将A表满足bu=“DCG” and country="JP"的数据，关联的C表中c.status='Approved’的那一条数据的c.time加上90天赋值给a.time

SQL实现

UPDATE  a a1
    LEFT JOIN b b1
    on a1.id = b1.a_id
    LEFT JOIN (SELECT * FROM c) c1
    on b1.id = c1.b_id
set a1.time=DATE_ADD(c1.time , INTERVAL 90  DAY)
where a1.bu='DCG' and country='JP' and a1.time is null;