Oracle拉链表（面试常问）

拉链表

可以保存历史数据的表（保存每一条数据的生命周期）

  a 100  2022-01-01  b 100  2022-01-01  a 200  2022-01-02  开链闭链a 100  2022-01-01  2022-01-02a 200  2022-01-02  9999-12-31b 100  2022-01-01  9999-12-31      某条数据开链日期 要作为 上一条数据的闭链日期  -- 创建原表CREATE TABLE SRC(      ID VARCHAR2(20),      YE NUMBER(20,2),      DT DATE);INSERT INTO SRC SELECT 'A',100,DATE '2022-01-01' FROM DUAL UNION ALL  SELECT 'B',100,DATE '2022-01-01' FROM DUAL;SELECT * FROM SRC;-- 01-02 A有存进去100INSERT INTO SRC SELECT 'A',200,DATE '2022-01-02' FROM DUAL;--中间表 （只存放每天新增的数据） 增量表CREATE TABLE ZL AS SELECT * FROM SRC WHERE 1 = 0;INSERT INTO ZL SELECT * FROM SRC WHERE DT = DATE '2022-01-02';SELECT * FROM ZL;-- 创建一个最终表CREATE TABLE LLB AS SELECT SRC.*,DATE '9999-12-31' ET FROM SRC;SELECT * FROM LLB;SELECT LLB.ID     ,LLB.YE     ,LLB.DT     ,(CASE WHEN ZL.DT > LLB.DT THEN ZL.DT  ELSE TO_DATE('99991231','YYYYMMDD') END)      FROM LLB LEFT JOIN ZL ON LLB.ID = ZL.ID-- 创建决策表  jcbCREATE TABLE JCB2 AS SELECT LLB.ID     ,LLB.YE     ,LLB.DT     ,(CASE WHEN ZL.DT > LLB.DT THEN ZL.DT  ELSE TO_DATE('99991231','YYYYMMDD') END) ET      FROM LLB LEFT JOIN ZL ON LLB.ID = ZL.IDSELECT * FROM JCB2 ORDER BY DT;-- 取01-02的数据SELECT * FROM JCB2 WHERE DT <= DATE '2022-01-02' AND ET > DATE '2022-01-02'