oracle 脚本实现表之间关联，以表中的时间戳生成

Question

表A
ID ZD1 ZD2
1 1 1
1 2 2

表B
AID ZD1 STIME ETIME
1 420 20141201 20150103
1 430 20150104 20151229
1 415 20151230 99991231

表C --表A的ZD1存储表C的ZD1
ZD1 ZD2 STIME ETIME
1 300 20131201 20150101
1 320 20150102 20160101
1 350 20160102 99991231
2 200 20140501 20150201
2 210 20150202 99991231

生成数据
STIME ETIME BZD1 CZD2 AID BZD1 BZD2
20141201 20150101 420 300 1 1 1
20150102 20150103 420 320 1 1 1
20150104 20151229 430 320 1 1 1
20151230 20160101 415 320 1 1 1
20160102 99991231 410 350 1 1 1

20141201 20150103 420 200 1 2 2
20150104 20150201 430 200 1 2 2
20150202 20151229 410 210 1 2 2
20151230 99991231 415 210 1 2 2

Answer 1

--创建表A
CREATE TABLE A (
  ID NUMBER,
  ZD1 NUMBER,
  ZD2 NUMBER
);

--插入数据到表A
INSERT INTO A VALUES (1, 1, 1);
INSERT INTO A VALUES (1, 2, 2);

--创建表B
CREATE TABLE B (
  AID NUMBER,
  ZD1 NUMBER,
  STIME NUMBER,
  ETIME NUMBER
);

--插入数据到表B
INSERT INTO B VALUES (1, 420, 20141201, 20150103);
INSERT INTO B VALUES (1, 430, 20150104, 20151229);
INSERT INTO B VALUES (1, 415, 20151230, 99991231);

--创建表C
CREATE TABLE C (
  ZD1 NUMBER,
  ZD2 NUMBER,
  STIME NUMBER,
  ETIME NUMBER
);

--插入数据到表C
INSERT INTO C VALUES (1, 300, 20131201, 20150101);
INSERT INTO C VALUES (1, 320, 20150102, 20160101);
INSERT INTO C VALUES (1, 350, 20160102, 99991231);
INSERT INTO C VALUES (2, 200, 20140501, 20150201);
INSERT INTO C VALUES (2, 210, 20150202, 99991231);

--生成数据
SELECT B.STIME, B.ETIME, B.ZD1 AS BZD1, C.ZD2 AS CZD2, B.AID, A.ZD1 AS AZD1, A.ZD2 AS AZD2
FROM A
JOIN B ON A.ID = B.AID
JOIN C ON A.ZD1 = C.ZD1 AND B.STIME <= C.ETIME AND B.ETIME >= C.STIME;