SQL查询和优化（十三）

来源：互联网发布：php array shift 编辑：程序博客网时间：2024/05/22 05:18

标量子查询与改写逻辑的一致性

当标量子查询中有聚合函数时，很多人在改写时，很喜欢把聚合函数写在主查询里，大概是认为这样简单。SELECT *     FROM (SELECT * FORM t1 WHERE col4_1 = '0') a    LEFT JOIN (SELECT col3_1,                        col3_2 coll,                         col2,                        SELECT DISTINCT col_1                            FROM t2                            WHERE col2_2 = a.col2                              AND col2_3 <= to_date('2012-09-03','YYYY-MM-DD')                              AND col2_4 >= to_date('2012-09-03','YYYY-MM-DD'))                FROM t3 a                 WHERE col2_3 <= to_date('2012-09-03','YYYY-MM-DD')                  AND col2_4 >= to_date('2012-09-03','YYYY-MM-DD'))b                ON a.col4 = b.col3_1;网友在改写这个查询时，直接把DISTINCT 放在了主查询SELECT关键字后面SELECT *     FROM (SELECT * FORM t1 WHERE col4_1 = '0') a    LEFT JOIN (SELECT DISTINCT a.col3_1,                      a.col3_2 coll,                       a.col2,                      b.col2_1 AS col3                FROM t3 a                JOIN (SELECT *                       FROM t2 b                      WHERE b.col2_3 <= to_date('2012-09-03','YYYY-MM-DD')                        AND b.col2_4 >= to_date('2012-09-03','YYYY-MM-DD')) b                    ON a.col2 =b.col2_2                    AND a.col2_3 <= to_date('2012-09-03','YYYY-MM-DD')                    AND a.col2_4 >= to_date('2012-09-03','YYYY-MM-DD')) b        ON a.col4 = b.col3_1;

笔者不赞成这么改写，因为改写之前有很多重复数据，改写之后就没有了，可以看下面的等价例子

原查询可以表示为：SELECT a.job,       a.dept,       (SELECT DISTINCT dname FROM dept2 b WHERE b.deptno = a.deptno) AS dename, FROM emp a  ORDER BY 1,2,3;按上面的方法更改：SELECT DISTINCT a.job,       a.deptno,       b.dname   FROM emp a     LEFT JOIN dept2 b ON b.deptno = a.deptno;除非确认主查询没有重复数据，否则不要这样改写，正确的改写如下：SELECT a.job,       a.deptno,       b.dname   FROM emp a     LEFT JOIN (SELECT dname,deptno FROM dept2 GROUP BY dname,deptno) b      ON b.deptno = a.deptno;

阅读全文

0 0