当前位置： 文江博客话题详情

不使用相关子查询重写查询

发布于 2024-12-17 02:45:59 字数 15925 浏览 1 评论 0原文

在 LINUX 上使用 Oracle 10gR2，我正在尝试调整以下查询。
我很确定摆脱相关子查询并可能使用一些分析函数可能是最佳方法，但我只是不明白——尤其是在 MAX( 上选择的嵌套相关子查询表_2.注释_日期）。任何帮助将不胜感激。谢谢。

EXPLAIN PLAN FOR
SELECT TABLE_4.INCIDENT_TYPE, 
   TABLE_4.POC_CONTACT, 
   (SELECT TABLE_2.NOTE_DATE 
           || ' ' 
           || TABLE_1.USER_FIRST_NAME 
           || ' ' 
           || TABLE_1.USER_LAST_NAME 
           || ' : ' 
           || TABLE_2.OTHER_HELP_NOTES 
    FROM   TABLE_1, 
           TABLE_2 
    WHERE  TABLE_2.USER_ID = TABLE_1.USER_ID 
           AND TABLE_2.REC_ID = TABLE_4.REC_ID 
           AND TABLE_2.NOTE_DATE = (SELECT MAX(TABLE_2.NOTE_DATE) 
                                              FROM   TABLE_2 
                                              WHERE  TABLE_2.REC_ID = TABLE_4.REC_ID 
                                                     AND TABLE_2.NOTE_DATE <= 
                                                         TABLE_4.REPORT_DATE)) 
                                                                 AS SUM_OF_SHORTAGE, 
   (SELECT TABLE_3.NOTE_DATE 
           || ' ' 
           || TABLE_1.USER_FIRST_NAME 
           || ' ' 
           || TABLE_1.USER_LAST_NAME 
           || ' : ' 
           || TABLE_3.HELP_NOTES 
    FROM   TABLE_1, 
           TABLE_3 
    WHERE  TABLE_3.USER_ID = TABLE_1.USER_ID 
           AND TABLE_3.REC_ID = TABLE_4.REC_ID 
           AND TABLE_3.NOTE_DATE = (SELECT MAX(TABLE_3.NOTE_DATE) 
                                                  FROM   TABLE_3 
                                                  WHERE  TABLE_3.REC_ID = TABLE_4.REC_ID 
                                                         AND TABLE_3.NOTE_DATE <= 
                                                             TABLE_4.REPORT_DATE)) AS HELP_NOTES, 
   TABLE_4.REPORT_NUM 
FROM   TABLE_4 
WHERE  TABLE_4.SITE_ID = '1';

@C:\ORACLE\PRODUCT\11.2.0\CLIENT_1\RDBMS\ADMIN\UTLXPLS.SQL;

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
PLAN HASH VALUE: 4036328474                                                                                                                                                                                                                                                                                  

------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
| ID  | OPERATION                     | NAME                       | ROWS  | BYTES | COST (%CPU)| TIME     |                                                                                                                                                                                                 
------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
|   0 | SELECT STATEMENT              |                            | 13009 |  2286K|   449   (2)| 00:00:06 |                                                                                                                                                                                                 
|*  1 |  FILTER                       |                            |       |       |            |          |                                                                                                                                                                                                 
|   2 |   NESTED LOOPS                |                            |     3 |   612 |     8   (0)| 00:00:01 |                                                                                                                                                                                                 
|   3 |    TABLE ACCESS BY INDEX ROWID| TABLE_2                    |     3 |   552 |     5   (0)| 00:00:01 |                                                                                                                                                                                                 
|*  4 |     INDEX RANGE SCAN          | IX_TABLE_2_REC_ID          |     3 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                 
|   5 |    TABLE ACCESS BY INDEX ROWID| TABLE_1                    |     1 |    20 |     1   (0)| 00:00:01 |                                                                                                                                                                                                 
|*  6 |     INDEX UNIQUE SCAN         | TABLE_1_PK                 |     1 |       |     0   (0)| 00:00:01 |                                                                                                                                                                                                 
|   7 |   SORT AGGREGATE              |                            |     1 |    13 |            |          |                                                                                                                                                                                                 
|*  8 |    TABLE ACCESS BY INDEX ROWID| TABLE_2                    |     1 |    13 |     5   (0)| 00:00:01 |                                                                                                                                                                                                 
|*  9 |     INDEX RANGE SCAN          | IX_TABLE_2_REC_ID          |     3 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                 
|* 10 |  FILTER                       |                            |       |       |            |          |                                                                                                                                                                                                 
|* 11 |   HASH JOIN                   |                            |    17 |  4063 |   482   (2)| 00:00:06 |                                                                                                                                                                                                 
|* 12 |    TABLE ACCESS FULL          | TABLE_3                    |    17 |  3723 |   474   (2)| 00:00:06 |                                                                                                                                                                                                 
|  13 |    TABLE ACCESS FULL          | TABLE_1                    |  1504 | 30080 |     8   (0)| 00:00:01 |                                                                                                                                                                                                 
|  14 |   SORT AGGREGATE              |                            |     1 |    13 |            |          |                                                                                                                                                                                                 
|* 15 |    TABLE ACCESS FULL          | TABLE_3                    |     1 |    13 |   474   (2)| 00:00:06 |                                                                                                                                                                                                 
|* 16 |  TABLE ACCESS FULL            | TABLE_4                    | 13009 |  2286K|   449   (2)| 00:00:06 |                                                                                                                                                                                                 
------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 

PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          

   1 - FILTER("TABLE_2"."NOTE_DATE"= (SELECT /*+ */ MAX("TABLE_2"."NOTE_DATE")                                                                                                                                                                                                           
              FROM "TABLE_2" "TABLE_2" WHERE "TABLE_2"."REC_ID"=:B1 AND                                                                                                                                                                                                        
              "TABLE_2"."NOTE_DATE"<=:B2))                                                                                                                                                                                                                                                         
   4 - ACCESS("TABLE_2"."REC_ID"=:B1)                                                                                                                                                                                                                                                              
   6 - ACCESS("TABLE_2"."USER_ID"="TABLE_1"."USER_ID")                                                                                                                                                                                                                                           
   8 - FILTER("TABLE_2"."NOTE_DATE"<=:B1)                                                                                                                                                                                                                                                          
   9 - ACCESS("TABLE_2"."REC_ID"=:B1)                                                                                                                                                                                                                                                              
  10 - FILTER("TABLE_3"."NOTE_DATE"= (SELECT /*+ */                                                                                                                                                                                                                                            
              MAX("TABLE_3"."NOTE_DATE") FROM "TABLE_3" "TABLE_3" WHERE                                                                                                                                                                                            
              "TABLE_3"."REC_ID"=:B1 AND "TABLE_3"."NOTE_DATE"<=:B2))                                                                                                                                                                                                            
  11 - ACCESS("TABLE_3"."USER_ID"="TABLE_1"."USER_ID")                                                                                                                                                                                                                                       
  12 - FILTER("TABLE_3"."REC_ID"=:B1)                                                                                                                                                                                                                                                          
  15 - FILTER("TABLE_3"."REC_ID"=:B1 AND "TABLE_3"."NOTE_DATE"<=:B2)                                                                                                                                                                                                             
  16 - FILTER("TABLE_4"."SITE_ID"=1)                                                                                                                                                                                                                                                            
 41 ROWS SELECTED

分解这个查询——关键问题似乎如下：

select REC_ID, TO_CHAR(REPORT_DATE,'DD-MON-YY HH:MI:SS') REPORT_DATE,  
(SELECT MAX(TABLE_2.note_date) as MAX_DATE
FROM   TABLE_2 
where  TABLE_2.REC_ID = TABLE_1.REC_ID 
       and TABLE_2.NOTE_DATE <= TABLE_1.REPORT_DATE
) NOTES_MAX_DATE
from TABLE_1 where REC_ID = 121 order by TO_DATE(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

输出需要与上面的相同。我尝试按如下方式创建连接：

SELECT TABLE_1.REC_ID, TO_CHAR(TABLE_1.REPORT_DATE,'DD-MON-YY HH:MI:SS') REPORT_DATE, MAX(TABLE_2.NOTE_DATE) AS NOTES_MAX_DATE
     FROM   TABLE_2, 
            TABLE_1 
     where  TABLE_2.REC_ID = TABLE_1.REC_ID
            AND TABLE_2.NOTE_DATE <= TABLE_1.REPORT_DATE 
            and ( TABLE_1.SITE_ID = '1' ) 
            and TABLE_1.REC_ID = 121
     group  by TABLE_1.REC_ID, TABLE_1.REPORT_DATE
     order by TO_DATE(REPORT_DATE,'DD-MON-YY HH:MI:SS');

但这会产生：

    REC_ID                 REPORT_DATE        NOTES_MAX_DATE        
---------------------- ------------------ ------------------------- 
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

所以我真的很困惑。有什么想法吗？ - 谢谢。

原文

Using Oracle 10gR2 on LINUX, I'm trying to tune the following query.
I'm pretty sure that getting rid of the correlated subqueries and the possible use of some analytic functions may be the optimal way to go, but I'm just not getting it -- especially with the nested correlated subquery that selects on the MAX(TABLE_2.NOTE_DATE). Any help would be much appreciated. Thanks.

EXPLAIN PLAN FOR
SELECT TABLE_4.INCIDENT_TYPE, 
   TABLE_4.POC_CONTACT, 
   (SELECT TABLE_2.NOTE_DATE 
           || ' ' 
           || TABLE_1.USER_FIRST_NAME 
           || ' ' 
           || TABLE_1.USER_LAST_NAME 
           || ' : ' 
           || TABLE_2.OTHER_HELP_NOTES 
    FROM   TABLE_1, 
           TABLE_2 
    WHERE  TABLE_2.USER_ID = TABLE_1.USER_ID 
           AND TABLE_2.REC_ID = TABLE_4.REC_ID 
           AND TABLE_2.NOTE_DATE = (SELECT MAX(TABLE_2.NOTE_DATE) 
                                              FROM   TABLE_2 
                                              WHERE  TABLE_2.REC_ID = TABLE_4.REC_ID 
                                                     AND TABLE_2.NOTE_DATE <= 
                                                         TABLE_4.REPORT_DATE)) 
                                                                 AS SUM_OF_SHORTAGE, 
   (SELECT TABLE_3.NOTE_DATE 
           || ' ' 
           || TABLE_1.USER_FIRST_NAME 
           || ' ' 
           || TABLE_1.USER_LAST_NAME 
           || ' : ' 
           || TABLE_3.HELP_NOTES 
    FROM   TABLE_1, 
           TABLE_3 
    WHERE  TABLE_3.USER_ID = TABLE_1.USER_ID 
           AND TABLE_3.REC_ID = TABLE_4.REC_ID 
           AND TABLE_3.NOTE_DATE = (SELECT MAX(TABLE_3.NOTE_DATE) 
                                                  FROM   TABLE_3 
                                                  WHERE  TABLE_3.REC_ID = TABLE_4.REC_ID 
                                                         AND TABLE_3.NOTE_DATE <= 
                                                             TABLE_4.REPORT_DATE)) AS HELP_NOTES, 
   TABLE_4.REPORT_NUM 
FROM   TABLE_4 
WHERE  TABLE_4.SITE_ID = '1';

@C:\ORACLE\PRODUCT\11.2.0\CLIENT_1\RDBMS\ADMIN\UTLXPLS.SQL;

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
PLAN HASH VALUE: 4036328474                                                                                                                                                                                                                                                                                  

------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
| ID  | OPERATION                     | NAME                       | ROWS  | BYTES | COST (%CPU)| TIME     |                                                                                                                                                                                                 
------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
|   0 | SELECT STATEMENT              |                            | 13009 |  2286K|   449   (2)| 00:00:06 |                                                                                                                                                                                                 
|*  1 |  FILTER                       |                            |       |       |            |          |                                                                                                                                                                                                 
|   2 |   NESTED LOOPS                |                            |     3 |   612 |     8   (0)| 00:00:01 |                                                                                                                                                                                                 
|   3 |    TABLE ACCESS BY INDEX ROWID| TABLE_2                    |     3 |   552 |     5   (0)| 00:00:01 |                                                                                                                                                                                                 
|*  4 |     INDEX RANGE SCAN          | IX_TABLE_2_REC_ID          |     3 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                 
|   5 |    TABLE ACCESS BY INDEX ROWID| TABLE_1                    |     1 |    20 |     1   (0)| 00:00:01 |                                                                                                                                                                                                 
|*  6 |     INDEX UNIQUE SCAN         | TABLE_1_PK                 |     1 |       |     0   (0)| 00:00:01 |                                                                                                                                                                                                 
|   7 |   SORT AGGREGATE              |                            |     1 |    13 |            |          |                                                                                                                                                                                                 
|*  8 |    TABLE ACCESS BY INDEX ROWID| TABLE_2                    |     1 |    13 |     5   (0)| 00:00:01 |                                                                                                                                                                                                 
|*  9 |     INDEX RANGE SCAN          | IX_TABLE_2_REC_ID          |     3 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                 
|* 10 |  FILTER                       |                            |       |       |            |          |                                                                                                                                                                                                 
|* 11 |   HASH JOIN                   |                            |    17 |  4063 |   482   (2)| 00:00:06 |                                                                                                                                                                                                 
|* 12 |    TABLE ACCESS FULL          | TABLE_3                    |    17 |  3723 |   474   (2)| 00:00:06 |                                                                                                                                                                                                 
|  13 |    TABLE ACCESS FULL          | TABLE_1                    |  1504 | 30080 |     8   (0)| 00:00:01 |                                                                                                                                                                                                 
|  14 |   SORT AGGREGATE              |                            |     1 |    13 |            |          |                                                                                                                                                                                                 
|* 15 |    TABLE ACCESS FULL          | TABLE_3                    |     1 |    13 |   474   (2)| 00:00:06 |                                                                                                                                                                                                 
|* 16 |  TABLE ACCESS FULL            | TABLE_4                    | 13009 |  2286K|   449   (2)| 00:00:06 |                                                                                                                                                                                                 
------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 

PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          

   1 - FILTER("TABLE_2"."NOTE_DATE"= (SELECT /*+ */ MAX("TABLE_2"."NOTE_DATE")                                                                                                                                                                                                           
              FROM "TABLE_2" "TABLE_2" WHERE "TABLE_2"."REC_ID"=:B1 AND                                                                                                                                                                                                        
              "TABLE_2"."NOTE_DATE"<=:B2))                                                                                                                                                                                                                                                         
   4 - ACCESS("TABLE_2"."REC_ID"=:B1)                                                                                                                                                                                                                                                              
   6 - ACCESS("TABLE_2"."USER_ID"="TABLE_1"."USER_ID")                                                                                                                                                                                                                                           
   8 - FILTER("TABLE_2"."NOTE_DATE"<=:B1)                                                                                                                                                                                                                                                          
   9 - ACCESS("TABLE_2"."REC_ID"=:B1)                                                                                                                                                                                                                                                              
  10 - FILTER("TABLE_3"."NOTE_DATE"= (SELECT /*+ */                                                                                                                                                                                                                                            
              MAX("TABLE_3"."NOTE_DATE") FROM "TABLE_3" "TABLE_3" WHERE                                                                                                                                                                                            
              "TABLE_3"."REC_ID"=:B1 AND "TABLE_3"."NOTE_DATE"<=:B2))                                                                                                                                                                                                            
  11 - ACCESS("TABLE_3"."USER_ID"="TABLE_1"."USER_ID")                                                                                                                                                                                                                                       
  12 - FILTER("TABLE_3"."REC_ID"=:B1)                                                                                                                                                                                                                                                          
  15 - FILTER("TABLE_3"."REC_ID"=:B1 AND "TABLE_3"."NOTE_DATE"<=:B2)                                                                                                                                                                                                             
  16 - FILTER("TABLE_4"."SITE_ID"=1)                                                                                                                                                                                                                                                            
 41 ROWS SELECTED

Breaking down this query -- the key problem seems to be the following:

select REC_ID, TO_CHAR(REPORT_DATE,'DD-MON-YY HH:MI:SS') REPORT_DATE,  
(SELECT MAX(TABLE_2.note_date) as MAX_DATE
FROM   TABLE_2 
where  TABLE_2.REC_ID = TABLE_1.REC_ID 
       and TABLE_2.NOTE_DATE <= TABLE_1.REPORT_DATE
) NOTES_MAX_DATE
from TABLE_1 where REC_ID = 121 order by TO_DATE(REPORT_DATE,'DD-MON-YY HH:MI:SS');

Which should return the following:

    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

The output needs to be the same as the above. I tried creating a join as follows:

SELECT TABLE_1.REC_ID, TO_CHAR(TABLE_1.REPORT_DATE,'DD-MON-YY HH:MI:SS') REPORT_DATE, MAX(TABLE_2.NOTE_DATE) AS NOTES_MAX_DATE
     FROM   TABLE_2, 
            TABLE_1 
     where  TABLE_2.REC_ID = TABLE_1.REC_ID
            AND TABLE_2.NOTE_DATE <= TABLE_1.REPORT_DATE 
            and ( TABLE_1.SITE_ID = '1' ) 
            and TABLE_1.REC_ID = 121
     group  by TABLE_1.REC_ID, TABLE_1.REPORT_DATE
     order by TO_DATE(REPORT_DATE,'DD-MON-YY HH:MI:SS');

But that yields:

    REC_ID                 REPORT_DATE        NOTES_MAX_DATE        
---------------------- ------------------ ------------------------- 
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

So I'm really stumped. Any ideas? -- Thanks.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

剩余の解释 2024-12-24 02:45:59

下面是 仅获取 max 一次的版本， 应删除相关子查询。它仍然使用子查询，但是，由于它们位于 FROM 子句而不是 SELECT 子句中，因此数据库应该更好地解决它们。也许也可以删除这些子查询，但这种方式更具可读性。该版本还使用 SQL-99 语法进行连接，这通常被认为是更可取的。

SELECT table_4.incident_type, 
       table_4.poc_contact, 
       t2.sum_of_shortage, 
       t3.help_notes, 
       table_4.report_num
FROM             table_4
       LEFT JOIN (SELECT table_2.rec_id,
                         table_2.note_date
                         || ' '
                         || table_1.user_first_name
                         || ' '
                         || table_1.user_last_name
                         || ' : '
                         || table_2.other_help_notes
                            AS sum_of_shortage
                  FROM   table_1 
                    JOIN table_2 
                      ON table_2.user_id = table_1.user_id
                  WHERE  table_2.note_date =
                            (SELECT MAX(table_2.note_date) AS max_date
                             FROM   table_2
                             WHERE  table_2.rec_id = table_4.rec_id 
                                AND table_2.note_date <= table_4.report_date)) t2
              ON t2.rec_id = table_4.rec_id
       LEFT JOIN (SELECT table_3.rec_id,
                         table_3.note_date
                         || ' '
                         || table_1.user_first_name
                         || ' '
                         || table_1.user_last_name
                         || ' : '
                         || table_3.other_help_notes
                            AS help_notes
                  FROM   table_1 
                    JOIN table_3 
                      ON table_3.user_id = table_1.user_id
                  WHERE  table_2.note_date =
                            (SELECT MAX(table_3.note_date) AS max_date
                             FROM   table_3
                             WHERE  table_3.rec_id = table_4.rec_id 
                                AND table_3.note_date <= table_4.report_date)) t3
              ON t3.rec_id = table_4.rec_id
WHERE  table_4.site_id = '1';

@shawno：你是对的， with 子句有缺陷，因为我误读了你的初始查询。以上是修正后的版本。由于 max 值特定于每行，因此您已用于获取这些值的方法可能是最有效的。优化此问题的最佳选择似乎是将子查询从 select 子句移动到 from 子句。

另外，这是一个未经测试的解决方案，因为我既没有您的表结构也没有您的数据。在不投入太多工作的情况下，我能做的最好的事情就是验证语法是否有效。

Below is a version that only gets the max once and should remove the correlated sub-queries. It does still use sub-queries, but, as they're in the FROM clause rather than the SELECT clause, the database should do a better job of resolving them. It's probably possible to remove those sub-queries as well, but it's more readable this way. This version also uses the SQL-99 syntax for joins, which is generally considered preferable.

SELECT table_4.incident_type, 
       table_4.poc_contact, 
       t2.sum_of_shortage, 
       t3.help_notes, 
       table_4.report_num
FROM             table_4
       LEFT JOIN (SELECT table_2.rec_id,
                         table_2.note_date
                         || ' '
                         || table_1.user_first_name
                         || ' '
                         || table_1.user_last_name
                         || ' : '
                         || table_2.other_help_notes
                            AS sum_of_shortage
                  FROM   table_1 
                    JOIN table_2 
                      ON table_2.user_id = table_1.user_id
                  WHERE  table_2.note_date =
                            (SELECT MAX(table_2.note_date) AS max_date
                             FROM   table_2
                             WHERE  table_2.rec_id = table_4.rec_id 
                                AND table_2.note_date <= table_4.report_date)) t2
              ON t2.rec_id = table_4.rec_id
       LEFT JOIN (SELECT table_3.rec_id,
                         table_3.note_date
                         || ' '
                         || table_1.user_first_name
                         || ' '
                         || table_1.user_last_name
                         || ' : '
                         || table_3.other_help_notes
                            AS help_notes
                  FROM   table_1 
                    JOIN table_3 
                      ON table_3.user_id = table_1.user_id
                  WHERE  table_2.note_date =
                            (SELECT MAX(table_3.note_date) AS max_date
                             FROM   table_3
                             WHERE  table_3.rec_id = table_4.rec_id 
                                AND table_3.note_date <= table_4.report_date)) t3
              ON t3.rec_id = table_4.rec_id
WHERE  table_4.site_id = '1';

@shawno: You're right, the with clause was flawed because I misread your initial query. Above is a corrected version. Because the max values are specific to each row, the method that you were already using to get those values is probably the most efficient. Your best option for optimizing this appears to just be moving the sub-queries from the select clause to the from clause.

Also, this is an untested solution, as I have neither your table structure nor your data. The best I can do without putting far too much work into it is to verified that the syntax is valid.

回复 原文
~没有更多了~

关于作者

森末i

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

尘曦

文章 0 评论 0

在梵高的星空下

文章 0 评论 0

善良天后

文章 0 评论 0

韬韬不绝

文章 0 评论 0

qq_CgiN62

文章 0 评论 0

不美如何

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文