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

最新推荐文章于 2024-09-28 02:37:01 发布

原创最新推荐文章于 2024-09-28 02:37:01 发布 · 745 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #优化

数据库专栏收录该内容

36 篇文章

订阅专栏

本文介绍了一种SQL查询优化技巧，即如何将复杂的标量子查询转换为更高效的分析函数，特别是当涉及到表的自关联场景时。通过具体示例展示了如何减少查询层级并提升性能。

当标量子查询中的表与主查询中的表一样，也就是自关联的时候，常常可以改为分析函数
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