xiaoxiaoCNDS 2020-04-20
最近在做一个简单的报表, 用的工具是帆软报表, 一开始觉得有点low, 现在看看还行, 除了界面真的太丑外, 其它还要, 这种大量要使用 sql 的方式, 我觉得是非常灵活高效的, I like . 这个过程 sql 确实也提高了不少. 其中有个需求, 是要做个数据联动, 报表之间的部分字段的联动变化.
两张表, B 表的某些字段要 跟随 A 表的变化实现级联, 也不是级联,就是完全同步.
分析了一波, 本以为要做个级联操作, 但一看, 其实就是, join 就能解决的呀.
首先要考虑 B 表原有的数据不能动, 然后再进行联动.. 还是建表等来说明整个原理吧.
drop table if exists tb_01; create table tb_01( id varchar(20) primary key, name varchar(20), age int, gender varchar(30), -- 默认值 alive varchar(30) not null default ‘alive‘ ); insert into tb_01(id, name, age, gender) values (‘100‘, ‘youge‘, 24, ‘男‘); insert into tb_01(id, name, age, gender) values (‘101‘, ‘xiaojie‘, 22, ‘女‘); insert into tb_01(id, name, age, gender) values (‘102‘, ‘tianer‘, 22, ‘女‘); drop table if exists tb_02; create table tb_02( id varchar(20) primary key, name varchar(20), age int, gender varchar(30), dream varchar(20), city varchar(20), income int, -- alive 有值表示同步中, 无值则表示离线状态 status varchar(20) null ); insert into tb_02(id) values (‘100‘); insert into tb_02(id) values (‘101‘); insert into tb_02(id) values (‘102‘); insert into tb_02(id) values (‘103‘);
初始数据如下:
-- preview mysql> select * from tb_01; +-----+---------+------+--------+ | id | name | age | gender | +-----+---------+------+--------+ | 100 | youge | 24 | 男 | | 101 | xiaojie | 22 | 女 | | 102 | tianer | 22 | 女 | +-----+---------+------+--------+ 3 rows in set (0.00 sec) mysql> select * from tb_02; +-----+------+------+--------+-------+------+--------+-------+ | id | name | age | gender | dream | city | income | alive | +-----+------+------+--------+-------+------+--------+-------+ | 100 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 101 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 102 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 103 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +-----+------+------+--------+-------+------+--------+-------+ 4 rows in set (0.00 sec)
先要让 B 表的数据不能动, 初始化的时候.
select a.id as a_id, b.id, b.name, b.age, b.gender, b.dream, b.city, b.income, b.alive from tb_01 as a right join tb_02 as b on a.id = b.id
left join 和 right join 灵活使用需要哦
B 表左连接 A 表 这个结果作为 C 表
+------+-----+------+------+--------+-------+------+--------+-------+ | a_id | id | name | age | gender | dream | city | income | alive | +------+-----+------+------+--------+-------+------+--------+-------+ | 100 | 100 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 101 | 101 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 102 | 102 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | NULL | 103 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------+-----+------+------+--------+-------+------+--------+-------+
A 表再 和 C 表做左连接 (同步新增等情况的的数据)
select a.id, a.name, a.age, a.gender, -- c.a_id, c.dream, c.city, c.income, c.alive from tb_01 as a left join ( select a.id as a_id, b.id, b.name, b.age, b.gender, b.dream, b.city, b.income, b.alive from tb_01 as a right join tb_02 as b on a.id = b.id ) as c on a.id = c.id -- A 表删减记录, 不会影响 B 表原有的 and c.a_id is not null
-- 左边作为作为联动标准(中间表01) +-----+---------+------+--------+-------+------+--------+-------+ | id | name | age | gender | dream | city | income | alive | +-----+---------+------+--------+-------+------+--------+-------+ | 100 | youge | 24 | 男 | NULL | NULL | NULL | NULL | | 101 | xiaojie | 22 | 女 | NULL | NULL | NULL | NULL | | 102 | tianer | 22 | 女 | NULL | NULL | NULL | NULL | +-----+---------+------+--------+-------+------+--------+-------+ 3 rows in set (0.00 sec)
将此中间表01, 主键插入到 目标表 tb_02
主键插入: 主键冲突则覆盖, 否则新增,即在帆软的 主键填报
mysql> select * from tb_02; +-----+---------+------+--------+-------+------+--------+-------+ | id | name | age | gender | dream | city | income | alive | +-----+---------+------+--------+-------+------+--------+-------+ | 100 | youge | 24 | 男 | NULL | NULL | NULL | NULL | | 101 | xiaojie | 22 | 女 | NULL | NULL | NULL | NULL | | 102 | tianer | 22 | 女 | NULL | NULL | NULL | NULL | | 103 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +-----+---------+------+--------+-------+------+--------+-------+ 4 rows in set (0.00 sec)
等待提交完后, 还要标记出, 哪些是同步的, 哪些是离线的。
即: 用 A 表 right join 目标表, 能匹配上的, 就是同步的呗。
-- step_02 (木丁就是 给 alive 字段打上标签) select b.id, b.name, b.age, b.gender, b.dream, b.city, b.income, -- 匹配上的 将 alive 填充为 该记录的ID a.id as alive from tb_01 as a right join tb_02 as b on a.id = b.id
+-----+---------+------+--------+-------+------+--------+-------+-------+ | id | name | age | gender | dream | city | income | alive | alive | +-----+---------+------+--------+-------+------+--------+-------+-------+ | 100 | youge | 24 | 男 | NULL | NULL | NULL | NULL | 100 | | 101 | xiaojie | 22 | 女 | NULL | NULL | NULL | NULL | 101 | | 102 | tianer | 22 | 女 | NULL | NULL | NULL | NULL | 102 | | 103 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +-----+---------+------+--------+-------+------+--------+-------+-------+ 4 rows in set (0.00 sec) => 再 提交一波 (主键插入) 则是最新的了。 mysql> select * from tb_02; +-----+---------+------+--------+-------+------+--------+-------+ | id | name | age | gender | dream | city | income | alive | +-----+---------+------+--------+-------+------+--------+-------+ | 100 | youge | 24 | 男 | NULL | NULL | NULL | NULL | | 101 | xiaojie | 22 | 女 | NULL | NULL | NULL | NULL | | 102 | tianer | 22 | 女 | NULL | NULL | NULL | NULL | | 103 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +-----+---------+------+--------+-------+------+--------+-------+ 4 rows in set (0.00 sec)
就是两步, 先左连接, 再右连接嘛, 中间需要2次主键插入. 虽然看着这里, sql 很多, 其实主要还是多在字段上, 逻辑非常清晰哦.
select a.id, a.name, a.age, a.gender, -- c.a_id, c.dream, c.city, c.income, c.alive from tb_01 as a left join ( select a.id as a_id, b.id, b.name, b.age, b.gender, b.dream, b.city, b.income, b.alive from tb_01 as a -- 共有的 left join tb_02 as b on a.id = b.id ) as c on a.id = c.id and c.a_id is not null 提交一波哦 !!! -- step_02 (木丁就是 给 alive 字段打上标签) select b.id, b.name, b.age, b.gender, b.dream, b.city, b.income, -- 匹配上的 将 alive 填充为 该记录的ID a.id as alive from tb_01 as a right join tb_02 as b on a.id = b.id
直接就一个左连接, (主键插入), 然后, 再来一个右连接, 匹配上的 id 给作为 行打上一个标签即可.
-- step_01: select a.*, b.dream, b.city, b.income, b.status from tb_01 as a left join tb_02 as b on a.id = b.id
-- step_02: -- A 表 跟 目标表 右连接, 能匹配上, 就将 -- A表的 id 填到 B 的 alive 字段中. select b.id, b.name, b.age, b.gender, b.dream, b.city, b.income, b.city, -- A 表的 id (匹配上) a.alive as status from tb_01 as a right join tb_02 as b on a.id = b.id
增改查必然是没有问题的的, 主要来看一波删除操作.
现在呢, 假设 A 表将 102 给删掉, 那最终的结果呢, 是在 B 表中, status 的状态 alive 变为 空 而已, 达到 逻辑删除 的效果, 这样也更能满足业务的回查数据的需要.
先一波 102 删除后, 中间表 step_01 的联动效果:
这时候, 目标表, 一旦 刷新 , 这状态就同步了呀.