как удалить коррелированный подзапрос

Я хотел бы переписать этот запрос, чтобы не использовать коррелированный подзапрос, но добиться того же результата запроса.

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. Спасибо!


sql subquery correlated-subquery
person shawno    schedule 28.11.2011    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Вы можете переписать его с помощью 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')
person Erwin Brandstetter    schedule 28.11.2011
comment
notes_max_date — это псевдоним столбца MAX(NOTE_DATE). В противном случае +1, потому что я бы пропустил левое внешнее соединение. - person Philip Kelley; 29.11.2011
comment
Потрясающий. Ожидаете ли вы, что новый запрос будет более производительным, написанным как соединение? Мне очень нравится видеть разные подходы к решению одних и тех же задач с помощью SQL. А как насчет использования какой-нибудь аналитической функции - возможно? Есть ли преимущества у такого подхода? Спасибо еще раз. - person shawno; 29.11.2011
comment
@shawno: с несколькими строками не должно быть большой разницы, может быть, даже подзапрос быстрее. Со многими строками JOIN должен быть быстрее. Но все зависит от используемой СУБД. - person Erwin Brandstetter; 29.11.2011