下面我们看一下各种求中间值的写法及性能比较。
1.数据准备
DROP table Student;
CREATE TABLE Student
(StuName VARCHAR2(10) NOT NULL,
Subject VARCHAR2(10),
Score INTEGER);
insert into Student values('A1','数学',60);
insert into Student values('A2','数学',90);
insert into Student values('A3','英语',80);
insert into Student values('A4','英语',40);
insert into Student values('A5','数学',70);
insert into Student values('A6','数学',80);
insert into Student values('A7','英语',80);
insert into Student values('A8','英语',100);
insert into Student values('A9','英语',70);
commit;
2.检索要求
求每个科目的中间值,
3.SQL写法及性能比较
select s1.Subject,s1.Score
from Student s1,Student s2
where s1.Subject=s2.Subject
group by s1.Subject,s1.Score
having sum(case when s1.Score<=s2.Score then 1 else 0 end)>=count(*)/2
and sum(case when s1.Score>=s2.Score then 1 else 0 end) >=count(*)/2;
with tmp as
(select s.SUBJECT
,s.Score
,ROW_NUMBER() over (partition by s.SUBJECT order by s.Score desc) as rn1
,ROW_NUMBER() over (partition by s.SUBJECT order by s.Score asc) as rn2
from Student s)
select distinct t.SUBJECT,t.Score
from tmp t
where t.rn1 in (rn2-1,rn2,rn2+1);
通过上面两种写法比较,分析函数减少一次表扫描,还是分析函数性能更好。