Oracle拉链表（面试常问）

拉链表

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

  a 100  2022-01-01
  b 100  2022-01-01
  a 200  2022-01-02
         开链       闭链
a 100  2022-01-01  2022-01-02
a 200  2022-01-02  9999-12-31
b 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有存进去100
INSERT 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

-- 创建决策表  jcb
CREATE 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.ID


SELECT * FROM JCB2 ORDER BY DT;
-- 取01-02的数据
SELECT * FROM JCB2 WHERE DT <= DATE '2022-01-02' AND ET > DATE '2022-01-02'