SQL作业

最新推荐文章于 2023-07-10 23:41:38 发布

憨憨少年不想秃头

最新推荐文章于 2023-07-10 23:41:38 发布

阅读量106

点赞数

文章标签： java 数据库开发语言

本文链接：https://blog.csdn.net/weixin_54664477/article/details/128029057

版权

-- 源表
DROP TABLE IF EXISTS SRC;
CREATE TABLE IF NOT EXISTS `SRC` (
  `ID` int(11) DEFAULT NULL,
  `NAME` varchar(255) DEFAULT NULL,
  `BAL`  int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- 拉链目标表
DROP TABLE IF EXISTS TAG;
CREATE TABLE IF NOT EXISTS `TAG` (
  `ID` int(11) DEFAULT NULL,
  `NAME` varchar(255) DEFAULT NULL,
  `BAL`  int(11) DEFAULT NULL,
  `START_DT`  Date DEFAULT NULL,
  `END_DT`  Date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- 当天数据存放临时表
drop table if exists VT_NEW;
create table if not exists VT_NEW like TAG; 
 
-- 当天数据新增变化临时表
drop table if exists VT_INC;
create table if not exists VT_INC like TAG;

源表数据插入

insert into SRC values(101,'王五',100);
insert into SRC values(102,'赵四',50);
+-----+------+------+
| ID  | NAME | BAL  |
+-----+------+------+
| 101 | 王五 |  100 |
| 102 | 赵四 |   50 |
+-----+------+------+

初始数据入目标表(第一次跑数,初始数据直接关链)

insert into TAG(id,name,bal,start_dt,end_dt)
select t.id,t.name,t.BAL,CURRENT_DATE,'2099-12-31'
from SRC t;
+-----+------+------+------------+------------+
| ID  | NAME | BAL  | START_DT   | END_DT     |
+-----+------+------+------------+------------+
| 101 | 王五 |  100 | 2022-11-24 | 2099-12-31 |
| 102 | 赵四 |   50 | 2022-11-24 | 2099-12-31 |
+-----+------+------+------------+------------+

源表数据插入及更新

update SRC set bal='1000' where id=101;
insert into SRC values(103,'张三',10);
+-----+------+------+
| ID  | NAME | BAL  |
+-----+------+------+
| 101 | 王五 | 1000 |
| 102 | 赵四 |   50 |
| 103 | 张三 |   10 |
+-----+------+------+

VT_NEW存放源数据,直接开链

drop table if exists VT_NEW;
create table if not exists VT_NEW like TAG; 
 
insert into  VT_NEW(ID,NAME,BAL,START_DT,END_DT)
SELECT    ID,NAME,BAL,date_add(CURRENT_DATE,interval 1 day) as START_DT /*模拟T+1*/,'2099-12-31' as END_DT
FROM    SRC ;
 
+-----+------+------+------------+------------+
| ID  | NAME | BAL  | START_DT   | END_DT     |
+-----+------+------+------------+------------+
| 101 | 王五 | 1000 | 2022-11-25 | 2099-12-31 |
| 102 | 赵四 |   50 | 2022-11-25 | 2099-12-31 |
| 103 | 张三 |   10 | 2022-11-25 | 2099-12-31 |
+-----+------+------+------------+------------+

VT_INC存放出对比出的新增及修改数据

drop table if exists VT_INC;
create table if not exists VT_INC like TAG; 
 
insert into VT_INC(ID,NAME,BAL,START_DT,END_DT)
select ID,NAME,BAL,START_DT,END_DT
from VT_NEW 
where (ID,NAME,BAL) not in (select ID,NAME,BAL from TAG where END_DT='2099-12-31');
 
+-----+------+------+------------+------------+
| ID  | NAME | BAL  | START_DT   | END_DT     |
+-----+------+------+------------+------------+
| 101 | 王五 | 1000 | 2022-11-25 | 2099-12-31 |
| 103 | 张三 |   10 | 2022-11-25 | 2099-12-31 |
+-----+------+------+------------+------------+

更新目标表需要做拉链的数据,将原数据关链

UPDATE TAG
SET END_DT = date_add(CURRENT_DATE,interval 1 day)/*模拟T+1*/
WHERE END_DT = '2099-12-31' AND ID IN (SELECT ID FROM VT_INC);
+-----+------+-----+------------+------------+
| ID  | NAME | BAL | START_DT   | END_DT     |
+-----+------+-----+------------+------------+
| 101 | 王五 | 100 | 2022-11-24 | 2022-11-25 |
| 102 | 赵四 |  50 | 2022-11-24 | 2099-12-31 |
+-----+------+-----+------------+------------+

修改目标表进行开链操作

insert into TAG(ID,NAME,BAL,START_DT,END_DT)
select ID,NAME,BAL,START_DT,'2099-12-31'
from VT_INC;
+-----+------+------+------------+------------+
| ID  | NAME | BAL  | START_DT   | END_DT     |
+-----+------+------+------------+------------+
| 101 | 王五 |  100 | 2022-11-24 | 2022-11-25 |
| 101 | 王五 | 1000 | 2022-11-24 | 2099-12-31 |
| 102 | 赵四 |   50 | 2022-11-24 | 2099-12-31 |
| 103 | 张三 |   10 | 2022-11-24 | 2099-12-31 |
+-----+------+------+------------+------------+

源表数据移除

delete from SRC where id=101;
+-----+------+-----+
| ID  | NAME | BAL |
+-----+------+-----+
| 102 | 赵四 |  50 |
| 103 | 张三 |  10 |
+-----+------+-----+

VT_NEW存放源数据,直接开链

drop table if exists VT_NEW;
create table if not exists VT_NEW like TAG; 
 
insert into  VT_NEW(ID,NAME,BAL,START_DT,END_DT)
SELECT    ID,NAME,BAL,date_add(CURRENT_DATE,interval 1 day) as START_DT /*模拟T+1*/,'2099-12-31' as END_DT
FROM    SRC ;
+-----+------+-----+------------+------------+
| ID  | NAME | BAL | START_DT   | END_DT     |
+-----+------+-----+------------+------------+
| 102 | 赵四 |  50 | 2022-11-25 | 2099-12-31 |
| 103 | 张三 |  10 | 2022-11-25 | 2099-12-31 |
+-----+------+-----+------------+------------+

VT_INC存放出对比出的新增及修改数据

drop table if exists VT_INC;
create table if not exists VT_INC like TAG; 
 
INSERT    INTO VT_INC(ID,NAME,BAL,START_DT,END_DT)
SELECT    ID,NAME,BAL,START_DT,date_add(CURRENT_DATE,interval 2 day) /*模拟T+2数据移除了*/
FROM    TAG
WHERE    END_DT = '2099-12-31'    AND (ID,NAME,BAL) NOT IN (
SELECT    ID,NAME,BAL 
FROM    VT_NEW ) ;
 
+-----+------+------+------------+------------+
| ID  | NAME | BAL  | START_DT   | END_DT     |
+-----+------+------+------------+------------+
| 101 | 王五 | 1000 | 2022-11-25 | 2022-11-26 |
+-----+------+------+------------+------------+

修改目标表进行关链操作

UPDATE TAG
SET END_DT = date_add(CURRENT_DATE,interval 2 day)/*模拟T+1*/
WHERE END_DT = '2099-12-31' AND ID IN (SELECT ID FROM VT_INC);
 
+-----+------+------+------------+------------+
| ID  | NAME | BAL  | START_DT   | END_DT     |
+-----+------+------+------------+------------+
| 101 | 王五 |  100 | 2022-11-24 | 2022-11-25 |
| 101 | 王五 | 1000 | 2022-11-25 | 2022-22-26 |
| 102 | 赵四 |   50 | 2022-11-24 | 2099-12-31 |
| 103 | 张三 |   10 | 2022-11-25 | 2099-12-31 |
+-----+------+------+------------+------------+