Having转Not Exists优化

Having如果相关查询，有时候会产生Filter，下面我们看看转换方法。

1.数据准备

DROP table Student;

CREATE TABLE Student

(StuName VARCHAR2(10) NOT NULL,

 SClass VARCHAR2(10) ,

 Subject VARCHAR2(10),

 Team VARCHAR2(10),

 Score INTEGER);

 

insert into Student values('A1','1','数学','A',60);

insert into Student values('A2','1','数学','B',90);

insert into Student values('A3','1','英语','A',80);

insert into Student values('A4','1','英语','B',40);

insert into Student values('A5','2','数学','A',60);

insert into Student values('A6','2','数学','B',60);

insert into Student values('A7','2','英语','B',80);

insert into Student values('A8','2','英语','B',100);

commit;

 

2.检索要求

检索每个班级中，科目平均分大于所有小组平均分的班级名，科目名，科目平均分

即上图黄色标注部分。

3.SQL优化

select s.SClass,s.Subject, avg(s.Score)

from Student s

group by s.SClass,s.Subject

having avg(s.score)

    >all(select avg(s1.Score)

            from Student s1

               where s.SClass=s1.SClass

               group by s1.Team);

Having过滤走了Filter连接。

with tmp as

(select s.SClass,

        s.Subject,

              avg(s.Score) over(partition by s.SClass,s.Subject) as avg_Subject,

              avg(s.score) over(partition by s.SClass,s.Team) as avg_Team

 from Student s)

select distinct t.SClass,t.Subject,t.avg_Subject

from tmp t

where not exists (select 1

                  from tmp t2

                              where t.SClass=t2.SClass

                                and t.avg_Subject<=t2.avg_Team);

查询把with查询展开了，然后走了Hash连接。

这里如果聚合运算可以大幅度减少数据量，建议with不展开。具体不在验证。