合并两个表并使用两个日期列复制行

Question

我有几张桌子：

CREATE TABLE TABLE_4 
(
    DATE_INI    DATE NOT NULL,
    DATE_FIN    DATE NOT NULL,
    TOT_CLIENT  NUMBER NOT NULL,
    LOCATOR NUMBER NOT NULL
);

INSERT INTO TABLE_4 (DATE_INI, DATE_FIN, TOT_CLIENT, LOCATOR) 
VALUES (to_date('19/9/23', 'RR/MM/DD'), to_date('19/9/26', 'RR/MM/DD'), 3, 758908);
INSERT INTO TABLE_4 (DATE_INI, DATE_FIN, TOT_CLIENT, LOCATOR) 
VALUES (to_date('19/9/23', 'RR/MM/DD'), to_date('19/9/26', 'RR/MM/DD'), 2, 765590);

CREATE TABLE TABLE_5 
( 
    LOCATOR     NUMBER NOT NULL,
    DATE_CLIENT DATE NOT NULL,
    NAME        VARCHAR2(250) NOT NULL
);

INSERT INTO TABLE_5 (LOCATOR, DATE_CLIENT, NAME) 
VALUES (758908, to_date('19/9/23', 'RR/MM/DD'), 'Maria');
INSERT INTO TABLE_5 (LOCATOR, DATE_CLIENT, NAME) 
VALUES (758908, to_date('19/9/23', 'RR/MM/DD'), 'Luis');
INSERT INTO TABLE_5 (LOCATOR, DATE_CLIENT, NAME) 
VALUES (758908, to_date('19/9/23', 'RR/MM/DD'), 'Pedro');
INSERT INTO TABLE_5 (LOCATOR, DATE_CLIENT, NAME) 
VALUES (765590, to_date('19/9/23', 'RR/MM/DD'), 'Manuel');
INSERT INTO TABLE_5 (LOCATOR, DATE_CLIENT, NAME) 
VALUES (765590, to_date('19/9/23', 'RR/MM/DD'), 'Ana');

CREATE SEQUENCE SEQ_TABLE_6  
MINVALUE 1 MAXVALUE 9999999999999999999999999999 
INCREMENT BY 1 
START WITH 2206 
NOCACHE ORDER NOCYCLE NOKEEP NOSCALE GLOBAL;

CREATE TABLE TABLE_6 
(
     ID NUMBER DEFAULT SEQ_TABLE_6.nextval  NOT NULL,
     ACTUAL_DATE    DATE NOT NULL,
     DATE_INI       DATE NOT NULL,
     DATE_FIN       DATE NOT NULL,
     LOCATOR        NUMBER NOT NULL,
     NAME           VARCHAR2(250) NOT NULL
);

有可能得到这个结果吗？

​

​

这样做的目的是：

1. 基于date_ini和date_fin创建行，创建这些列之间的所有日期。就像你在table_6上看到的“table_6”
2. 在每个日期行复制关于table_5的信息

有人能帮我处理这个问题吗？

问候

Answer 1

您也可以对子查询分解做同样的操作，如下所示

        WITH data
         AS (SELECT date_ini + column_value - 1 AS dates,
                    date_ini,
                    date_fin,
                    tot_client,
                    locator
             FROM   table_4
                    cross join TABLE(Cast(MULTISET (SELECT LEVEL
                                              FROM   dual
                                              CONNECT BY date_ini + LEVEL <=
                                                         date_fin + 1
                                                   )
                                          AS
         sys.ODCINUMBERLIST))),
         data1
         AS (SELECT dates,
                    date_ini,
                    date_fin,
                    locator
             FROM   data
             ORDER  BY dates,
                       date_ini,
                       date_fin,
                       locator)
    SELECT d1.*,
           t5.name
    FROM   data1 d1,
           table_5 t5
    WHERE  d1.locator = t5.locator 
    and d1.date_ini=t5.date_client

产出如下

        DATES   DATE_INI    DATE_FIN    LOCATOR NAME
    23-SEP-19   23-SEP-19   26-SEP-19   758908  Pedro
    23-SEP-19   23-SEP-19   26-SEP-19   758908  Maria
    23-SEP-19   23-SEP-19   26-SEP-19   758908  Luis
    23-SEP-19   23-SEP-19   26-SEP-19   765590  Manuel
    23-SEP-19   23-SEP-19   26-SEP-19   765590  Ana
    24-SEP-19   23-SEP-19   26-SEP-19   758908  Pedro
    24-SEP-19   23-SEP-19   26-SEP-19   758908  Luis
    24-SEP-19   23-SEP-19   26-SEP-19   758908  Maria
    24-SEP-19   23-SEP-19   26-SEP-19   765590  Ana
    24-SEP-19   23-SEP-19   26-SEP-19   765590  Manuel
    25-SEP-19   23-SEP-19   26-SEP-19   758908  Maria
    25-SEP-19   23-SEP-19   26-SEP-19   758908  Luis
    25-SEP-19   23-SEP-19   26-SEP-19   758908  Pedro
    25-SEP-19   23-SEP-19   26-SEP-19   765590  Ana
    25-SEP-19   23-SEP-19   26-SEP-19   765590  Manuel
    26-SEP-19   23-SEP-19   26-SEP-19   758908  Maria
    26-SEP-19   23-SEP-19   26-SEP-19   758908  Luis
    26-SEP-19   23-SEP-19   26-SEP-19   758908  Pedro
    26-SEP-19   23-SEP-19   26-SEP-19   765590  Ana
    26-SEP-19   23-SEP-19   26-SEP-19   765590  Manuel