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

╰+攻爆jí腚メ 2022-11-21 15:27 139阅读 0赞

### 目录 ###

*  创建模拟表
 *  业务需求
 *  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;