Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use LEFT JOIN to optimize multiple sub-queries in the database, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

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) 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

Direction of optimization: merge multiple tweeasset _ file subqueries

Optimization method: use left join to replace the subquery, put the associated columns in group by, and use case when for different conditions in the subquery

SELECT COUNT (1) num FROM (SELECT t1.* FROM t_asset T1, (SELECT asset_code, MAX (CASE WHEN status IN (1,10) eleven, 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

Direction of optimization: merge duplicate subqueries

SELECT m.khbh, d.wzzbm, wmsys.wm_concat (m.rkid) rkids FROM m@dblink m, d@dblink d, (select m1.zxdid sum_xysl from m@dblink 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 col3sfrom d.col2 m.col3=d.col3and m.col6=2and m.col7 wmsys. Wmpen concat (m.col3)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.