小编给大家分享一下数据库中如何使用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)优化方向:
(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(+)以上是“数据库中如何使用LEFT JOIN优化多个子查询”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注创新互联-成都网站建设公司行业资讯频道!
本文题目:数据库中如何使用LEFTJOIN优化多个子查询-创新互联
文章转载:http://bjjierui.cn/article/jpshi.html