拉链表

CREATE TABLE `policy` (
`policy_no` varchar(30) NOT NULL COMMENT '保单号',
`identify_number` varchar(22) NOT NULL COMMENT '身份证号',
`insurance_amount` decimal(25,2) DEFAULT NULL COMMENT '保额',
`insurance_premium` decimal(25,2) DEFAULT NULL COMMENT '保费',
`data_date` date DEFAULT NULL COMMENT '数据日期',
`chain_start_date` date DEFAULT NULL COMMENT '拉链开始日期',
`chain_end_date` date DEFAULT NULL COMMENT '拉链结束日期'
);

CREATE TABLE `policy_01` (
`policy_no` varchar(30) NOT NULL COMMENT '保单号',
`identify_number` varchar(22) NOT NULL COMMENT '身份证号',
`insurance_amount` decimal(25,2) DEFAULT NULL COMMENT '保额',
`insurance_premium` decimal(25,2) DEFAULT NULL COMMENT '保费',
`data_date` date DEFAULT NULL COMMENT '数据日期',
`chain_start_date` date DEFAULT NULL COMMENT '拉链开始日期',
`chain_end_date` date DEFAULT NULL COMMENT '拉链结束日期'
);

CREATE TABLE `policy_tmp` (
`policy_no` varchar(30) NOT NULL COMMENT '保单号',
`identify_number` varchar(22) NOT NULL COMMENT '身份证号',
`insurance_amount` decimal(25,2) DEFAULT NULL COMMENT '保额',
`insurance_premium` decimal(25,2) DEFAULT NULL COMMENT '保费',
`data_date` date DEFAULT NULL COMMENT '数据日期'
);

INSERT INTO policy_01 --目标临时表
SELECT policy_no,identify_number,insurance_amount,insurance_premium,data_date,data_date AS chain_start_date,'2999-12-31' AS chain_end_date FROM policy_tmp --增量表
UNION ALL
SELECT t1.policy_no,t1.identify_number,t1.insurance_amount,t1.insurance_premium,t1.data_date,t1.chain_start_date,CASE WHEN t1.chain_end_date='2999-12-31' AND t2.policy_no IS NOT NULL THEN t2.data_date ELSE t1.chain_end_date END AS chain_end_date
FROM policy t1 --目标表
LEFT JOIN policy_tmp t2 --增量表
ON t1.policy_no=t2.policy_no;

TRUNCATE TABLE policy; --目标表
INSERT INTO policy SELECT * FROM policy_01;
TRUNCATE TABLE policy_01; --目标临时表

--重新追数时
INSERT INTO policy_01 --目标临时表
SELECT policy_no,identify_number,insurance_amount,insurance_premium,data_date,data_date AS chain_start_date,'2999-12-31' AS chain_end_date FROM policy_tmp --增量表
UNION ALL
SELECT t1.policy_no,t1.identify_number,t1.insurance_amount,t1.insurance_premium,t1.data_date,t1.chain_start_date,CASE WHEN t1.chain_end_date='2999-12-31' AND t2.policy_no IS NOT NULL THEN t2.data_date ELSE t1.chain_end_date END AS chain_end_date
FROM policy t1 --目标表
LEFT JOIN policy_tmp t2 --增量表
ON t1.policy_no=t2.policy_no
where t1.chain_start_date<重新追数日期; --追数开始时考虑当时数据要 < policy_tmp 日期，之后就不需要了。

TRUNCATE TABLE policy; --目标表
INSERT INTO policy SELECT * FROM policy_01;
TRUNCATE TABLE policy_01; --目标临时表