Как удалить коррелированный подзапрос
Я хотел бы переписать этот запрос, чтобы не использовать коррелированный подзапрос, но добиться того же результата запроса.
CREATE TABLE "TABLE_1"
( "SITE_ID" NUMBER(*,0),
"USER_ID" NUMBER(*,0),
"REC_ID" NUMBER,
"REPORT_DATE" DATE
) ;
CREATE TABLE "TABLE_2"
( "SITE_ID" NUMBER,
"NOTE_DATE" DATE,
"NOTES" VARCHAR2(2000 BYTE),
"USER_ID" NUMBER,
"REC_ID" NUMBER
) ;
CREATE TABLE "TABLE_3"
( "SITE_ID" NUMBER,
"NOTE_DATE" DATE,
"HELP_NOTES" VARCHAR2(2000 BYTE),
"USER_ID" NUMBER,
"REC_ID" NUMBER
) ;
REM INSERTING into TABLE_1
Insert into TABLE_1 (SITE_ID,USER_ID,REC_ID,REPORT_DATE) values (1,6165,121,to_date('17-APR-10','DD-MON-RR'));
Insert into TABLE_1 (SITE_ID,USER_ID,REC_ID,REPORT_DATE) values (1,6165,121,to_date('01-MAY-10','DD-MON-RR'));
Insert into TABLE_1 (SITE_ID,USER_ID,REC_ID,REPORT_DATE) values (1,6165,121,to_date('08-MAY-10','DD-MON-RR'));
Insert into TABLE_1 (SITE_ID,USER_ID,REC_ID,REPORT_DATE) values (1,6165,121,to_date('24-APR-10','DD-MON-RR'));
Insert into TABLE_1 (SITE_ID,USER_ID,REC_ID,REPORT_DATE) values (1,6165,121,to_date('15-MAY-10','DD-MON-RR'));
Insert into TABLE_1 (SITE_ID,USER_ID,REC_ID,REPORT_DATE) values (1,6165,121,to_date('05-JUN-10','DD-MON-RR'));
Insert into TABLE_1 (SITE_ID,USER_ID,REC_ID,REPORT_DATE) values (1,6165,121,to_date('22-MAY-10','DD-MON-RR'));
Insert into TABLE_1 (SITE_ID,USER_ID,REC_ID,REPORT_DATE) values (1,6165,121,to_date('29-MAY-10','DD-MON-RR'));
REM INSERTING into TABLE_2
Insert into TABLE_2 (SITE_ID,NOTE_DATE,NOTES,USER_ID,REC_ID) values (1,to_date('13-APR-10','DD-MON-RR'),'Notes - we need stuff.',6165,121);
REM INSERTING into TABLE_3
Insert into TABLE_3 (SITE_ID,NOTE_DATE,HELP_NOTES,USER_ID,REC_ID) values (1,to_date('17-MAY-10','DD-MON-RR'),'Entry #1',1932,121);
Insert into TABLE_3 (SITE_ID,NOTE_DATE,HELP_NOTES,USER_ID,REC_ID) values (1,to_date('12-MAY-10','DD-MON-RR'),'Entry #2',6005,121);
Insert into TABLE_3 (SITE_ID,NOTE_DATE,HELP_NOTES,USER_ID,REC_ID) values (1,to_date('25-MAY-10','DD-MON-RR'),'Entry #3',1932,121);
ALTER TABLE "TABLE_1" MODIFY ("REC_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Constraints for Table TABLE_2
--------------------------------------------------------
ALTER TABLE "TABLE_2" MODIFY ("SITE_ID" NOT NULL ENABLE);
ALTER TABLE "TABLE_2" MODIFY ("REC_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Constraints for Table TABLE_3
--------------------------------------------------------
ALTER TABLE "TABLE_3" MODIFY ("SITE_ID" NOT NULL ENABLE);
ALTER TABLE "TABLE_3" MODIFY ("REC_ID" NOT NULL ENABLE);
Запрос выглядит следующим образом:
SELECT
TABLE_1.REC_ID,
TO_CHAR(table_1.REPORT_DATE, 'DD-MON-YY HH:MI:SS') report_date,
(
SELECT
MAX(table_3.NOTE_DATE) AS MAX_DATE
FROM
table_3
WHERE
table_3.REC_ID = table_1.REC_ID
AND table_3.NOTE_DATE <= table_1.REPORT_DATE
)
notes_max_date
FROM
table_1
ORDER BY
To_date(table_1.REPORT_DATE, 'DD-MON-YY HH:MI:SS')
И вывод должен быть следующим:
REC_ID REPORT_DATE NOTES_MAX_DATE
---------------------- ------------------ -------------------------
121 17-APR-10 12:30:00
121 24-APR-10 12:30:00
121 01-MAY-10 12:30:00
121 08-MAY-10 12:30:00
121 15-MAY-10 12:30:00 12-MAY-10
121 22-MAY-10 12:30:01 17-MAY-10
121 29-MAY-10 12:30:01 25-MAY-10
121 05-JUN-10 12:30:00 25-MAY-10
8 rows selected
Вывод должен состоять из 8 строк и включать нули в столбец NOTES_MAX_DATE. Спасибо!
1 ответ
Решение
Вы могли бы переписать это с LEFT JOIN а также GROUP BY как это:
SELECT t1.REC_ID
,to_char(t1.REPORT_DATE, 'DD-MON-YY HH:MI:SS') AS report_date
,max(t3.NOTE_DATE) AS notes_max_date
FROM table_1 AS t1
LEFT JOIN table_3 AS t3 ON t3.REC_ID = t1.REC_ID
AND t3.NOTE_DATE <= t1.REPORT_DATE
GROUP BY t1.REC_ID
,to_char(t1.REPORT_DATE, 'DD-MON-YY HH:MI:SS')
ORDER BY to_date(t1.REPORT_DATE, 'DD-MON-YY HH:MI:SS')