SQL查询和优化（十）

来源：互联网发布：c语言 final 编辑：程序博客网时间：2024/06/07 12:51

标量子查询改为LEFT JOIN
当标量中有ROWNUM = 1时

SELECT s.sid,        s.sname,        (SELECT cid FROM b WHERE b.sid = s.sid AND b.status IN('1','3') AND b.sstpe = '6' AND rownum = 1 ) cid,        (SELECT cid FROM b WHERE b.sid = s.sid AND b.status IN('1','3') AND b.sstpe = '8' AND rownum = 1 ) ringcid,        (SELECT cid FROM b WHERE b.sid = s.sid AND b.status IN('1','3') AND b.sstpe = '1' AND rownum = 1 ) mvcid    FROM s    WHERE 1=1      AND EXISTS( SELECT 1 FROM b c WHERE c.sid = s.sid AND status IN('1','3') AND rownum = 1 )      AND NOT EXISTS (SELECT 1       FROM b c       WHERE c.sstype = '8'      AND c.price ='0'      AND c.distributionarea !='99'      AND c.distributionarea IS NOT NULL      AND s.sid =c.sid);

不加排序的句子中直接用”rownum = 1”,本身就是对数据的要求不严格，只要有一条数据返回就行。所以标量子查询可以改成下面的语句

        (SELECT MAX( cid ) FROM b WHERE b.sid = s.sid AND b.status IN('1','3') AND b.sstpe = '6' ) cid,        (SELECT MAX( cid ) FROM b WHERE b.sid = s.sid AND b.status IN('1','3') AND b.sstpe = '8' ) ringcid,        (SELECT MAX( cid ) FROM b WHERE b.sid = s.sid AND b.status IN('1','3') AND b.sstpe = '1' ) mvcid,

增加聚合函数MAX，去掉条件rownum = 1，这样更改还能保证同样的数据返回同样的结果。下一步就是用一个查询返回上面三列及关联列的信息

把同样的条件放在WHERE后面，不同的条件放在CASE WHEN中SELECT c.sid,    MAX(CASE WHEN c.sstype = '1'  THEN cid END) AS mvcid,    MAX(CASE WHEN c.sstype = '8'  THEN cid END) AS ringid,    MAX(CASE WHEN c.sstype = '6'  THEN cid END) AS cidFROM b c WHERE status IN ('1','3')CROUP BY c.sid

然后再左联上面的结果就可以了

SELECT  s.sid,s.sname,c.cid,c.ringcid,c.mvcid  FROM s  INNER JOIN(SELECT c.sid,                MAX(CASE WHEN c.sstype = '1'  THEN cid END) AS mvcid,                MAX(CASE WHEN c.sstype = '8'  THEN cid END) AS ringid,                MAX(CASE WHEN c.sstype = '6'  THEN cid END) AS cid            FROM b c             WHERE status IN ('1','3')            CROUP BY c.sid) c ON (c.sid = s.sid) WHERE 1=1      AND NOT EXISTS (SELECT 1       FROM b c       WHERE c.sstype = '8'      AND c.price ='0'      AND c.distributionarea !='99'      AND c.distributionarea IS NOT NULL      AND s.sid =c.sid);

选自《Oracle 查询优化改写技巧与案例》有教无类落落著

阅读全文

0 0