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