用分析函数优化标量子查询

原语句如下

SELECT ii.*,
       CASE
         WHEN (SELECT COUNT(1)
                 FROM ii
                WHERE ii.id > 0
                  AND ii.flag = 2
                  AND ii.i_code = ii.i_code
                  AND ii.c_id NOT IN
                      (SELECT c_id
                         FROM c
                        WHERE ig_name LIKE '%停用%')) > 1 THEN
          2
         ELSE
          1
       END AS mulinv
  FROM ii
 WHERE (ii.id > 0 AND itemdesc LIKE :1 AND ii.isphantom <> :2)
   AND ii.c_id = :3
 ORDER BY ii.i_code, ii.i_name, ii. d_id

经询问 c.c_id是主键，这样就可以改写为left join

SELECT *
  FROM (SELECT ii.*,
               CASE
                 /*用分析函数代替标量自联接*/
                 WHEN (SUM(CASE WHEN flag = 2 AND c.c_id IS NULL THEN 1 END) over(PARTITION BY ii.i_code)) > 1 THEN
                  2
                 ELSE
                  1
               END AS mulinv
          FROM ii
          /*因c.cid为主键，所以可改为left join而不必担心主查询数据会翻倍*/
          LEFT JOIN c ON (c.c_id = ii.c_id AND c.ig_name LIKE '%停用%')
         /*为了保证分析函数窗口内数据与原标量范围一致，这儿的过滤条件要保持一致*/
         WHERE ii.id > 0) 
 /*提取出原标量所需数据后再应用其它的过滤条件*/
 WHERE itemdesc LIKE :1
   AND ii.isphantom <> :2
   AND ii.c_id = :3
 ORDER BY ii.i_code, ii.i_name, ii. d_id;