SQL查询和优化（十四）——用分析函数优化标量子查询

最新推荐文章于 2022-10-16 12:34:19 发布

every__day

最新推荐文章于 2022-10-16 12:34:19 发布

阅读量667

点赞数

分类专栏：数据库文章标签： sql 优化

本文链接：https://blog.csdn.net/every__day/article/details/77279385

版权

数据库专栏收录该内容

36 篇文章 0 订阅

订阅专栏

当标量子查询中的表与主查询中的表一样，也就是自关联的时候，常常可以改为分析函数
SELECT a.*,
       CASE
         WHEN(SELECT COUNT(1)
               FROM ii b
               WHERE b.id > 0
               AND b.flag = 2
               AND b.i_code = a.i_code
               AND b.c_id NOT IN
                   (SELECT c_id
                      FROM c
                      WHERE ig_name LIKE '%停用%')) > 1
         THEN 2
         ELSE 1
       END AS mulinv
    FROM ii a 
    WHERE (a.id > 0 AND itemdesc LIKE : 1 AND a.isphantom <> : 2)
     AND a.c_id = :3
    ORDER BY a.i_code, a.i_name,a.d_id


主查询的过滤条件比标量多，所以要嵌套一次，再应用主查询中多出来的过滤条件
SELECT * 
  FROM(SELECT ii.*,
              CASE
              /*用分析函数代替标量自连接*/
               WHEN(SUM(CASE WHEN flag = 2 AND c.cid IS NULL THEN 1 END)over(PARTITION BY ii.i_code)) > 1 THEN 2
               ELSE 1
              END AS mulinv
            FROM ii
            LEFT JOIN c ON(c.c_id = ii.c_id AND ig_name LIKE '%停用%')
            WHERE ii.id > 0)
    WHERE itemdesc LIKE : 1 
     AND ii.isphantom <>:2
     AND ii.c_id = :3
    ORDER BY a.i_code, a.i_name,a.d_id