当标量子查询中的表与主查询中的表一样,也就是自关联的时候,常常可以改为分析函数
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
SQL查询和优化(十四)——用分析函数优化标量子查询
最新推荐文章于 2022-10-16 12:34:19 发布