数据库中如何使用LEFT JOIN优化多个子查询

小编给大家分享一下数据库中如何使用LEFT JOIN优化多个子查询，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

1.SQL1

OLD:

SELECT COUNT (1) num  FROM (  SELECT t1.*            FROM t_asset t1           WHERE     1 = 1                 AND t1.TYPE = 0                 AND (   t1.status IN (1,                                       10,                                       11,                                       12,                                       100)                      OR (EXISTS                             (SELECT b.resource_id                                FROM t_asset_file b                               WHERE     t1.resource_id = b.asset_code                                     AND t1.status IN (3, 4, 8)                                     AND b.status IN (1,                                                      10,                                                      11,                                                      12))))                 AND (   EXISTS                            (SELECT 1                               FROM t_asset_file a1                              WHERE     t1.resource_id = a1.asset_code                                    AND (   a1.content_status = 1                                         OR a1.content_status = 4))                      OR NOT EXISTS                            (SELECT 1                               FROM t_asset_file a1                              WHERE t1.resource_id = a1.asset_code))        ORDER BY t1.create_time DESC, t1.resource_id) a;

优化方向: 合并多个t_asset_file子查询

优化方法: 使用left join 来代替子查询，把关联列放在group by中，将子查询中不同条件使用case when

SELECT COUNT (1) num  FROM (  SELECT t1.*            FROM t_asset t1,                 (  SELECT asset_code,                           MAX (CASE                                   WHEN status IN (1,                                                   10,                                                   11,                                                   12)                                   THEN                                      1                                   ELSE                                      0                                END)                              status,                           MAX (                              CASE                                 WHEN content_status = 1 OR content_status = 4                                 THEN                                    1                                 ELSE                                    0                              END)                              content_status                      FROM t_asset_file                  GROUP BY asset_code) t2           WHERE     t1.resource_id = t2.asset_code(+)                 AND t1.TYPE = 0                 AND (   t1.status IN (1,                                       10,                                       11,                                       12,                                       100)                      OR (t1.status IN (3, 4, 8) AND t2.status(+) = 1))                 AND ( (t2.content_status(+) = 1) OR (t2.asset_code IS NULL))        ORDER BY t1.create_time DESC, t1.resource_id) a;

2.SQL2

OLD:

  SELECT m.khbh, d.wzzbm, wmsys.wm_concat (m.rkid) rkids    FROM m@dblink m, d@dblink d   WHERE     m.rkid = d.rkid         AND m.rkzt = 2         AND m.ssny < '201311'         AND m.zxdid IS NULL         AND (   NOT EXISTS                        (SELECT 1                           FROM m@dblink m1, d@dblink d1                          WHERE     m1.rkid = d1.rkid                                AND m1.zxdid = m.rkid                                AND d1.wzzbm = d.wzzbm                                AND m1.rkzt = 3)              OR (SELECT SUM (d1.xysl)                    FROM m@dblink m1, d@dblink d1                   WHERE     m1.rkid = d1.rkid                         AND m1.zxdid = m.rkid                         AND d1.wzzbm = d.wzzbm                         AND m1.rkzt = 3) < d.xysl)GROUP BY m.khbh, d.wzzbm;

优化方向:合并重复的子查询

  SELECT m.khbh, d.wzzbm, wmsys.wm_concat (m.rkid) rkids    FROM m@dblink m, d@dblink d,                (select m1.zxdid,d1.wzzbm,                                sum(d1.xysl) sum_xysl                 from m@dblink m1,d@dblink d1                 where m1.rkzt = 3                 and m1.rkid = d1.rkid                 group by m1.zxdid,d1.wzzbm) z   WHERE     m.rkid = d.rkid         AND m.rkzt = 2         AND m.ssny < '201311'         AND m.zxdid IS NULL                 and z.zxdid=m.rkid                 and z.wzzbm=d.wzzbm         AND ( (z.zxdid is null and z.wzzbm is null)              OR z.sum_xysl < d.xysl)GROUP BY m.khbh, d.wzzbm;

3.SQL3

OLD:

select m.col1,d.col2,wmsys.wm_concat(m.col3) col3sfrom m,dwhere m.col3=d.col3and m.col6=2and m.col7<'201312'and m.col4 is nulland (not exists (select 1 from m m1,d d1                                  where m1.col3=d1.col3                                 and m1.col4=m.col3                                 and d1.col2=d.col2                                 and m1.col7< '201312'                                 and m1.col6=3) or         (select sum(d1.col5)          from m m1,d d1          where m1.col3=d1.col3          and m1.col4=m.col3          and d1.col2=d.col2          and m1.col7<'201312'          and m1.col6=3)
优化方向:
(1).主查询和子查询涉及表相同，并且条件有很多共同点，可以考虑合并。
(2).子查询结构相似，考虑使用left join来进行合并。
NEW:
with aa as(select m.col1,d.col2,m.col3,m.col4,d1.col5 from m,dwhere m.col3=d.col3and m.col6 in(2,3)and m1.col7< '201312')select aa.col1,aa.col2, wmsys.wm_concat(aa.col3) col3sfrom aa,         (select col4,col2,sum(d1.col5) sum_col5                from aa          where col6=3          group by col4,col2) bbwhere aa.col3=bb.col4(+)and aa.col2=bb.col2(+)and aa.col4 is nulland aa.col6=2and (bb.col4 is null or bb.sum_col5(+)
以上是"数据库中如何使用LEFT JOIN优化多个子查询"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！