SQL求最大值的几种方式

有表如下：

create table students
(sno int,
sname varchar(10),
age int);


insert into students values(1,'AARON',20);
insert into students values(2,'CHUCK',21);
insert into students values(3,'DOUG',20);
insert into students values(4,'MAGGIE',19);
insert into students values(5,'STEVE',22);
insert into students values(6,'JING',18);
insert into students values(7,'BRIAN',21);
insert into students values(8,'KAY',20);
insert into students values(9,'GILLIAN',20);
insert into students values(10,'CHAD',21);

 

求年龄最大的学生？

 

方式一，我的第一反应是使用聚焦函数max，相信很多人和我一样。

select * from students where age=(select max(age) from students);

 简单明了，两次全表扫描，成本6

 

方式二，有没有不使用max的方法来求最大呢？使用自连接加比较。

select * from students 
where age not in (select a.age from students a, students b where a.age< b.age);

 先做笛卡尔集，求年龄比任一个小的，再排除他们。三次全表扫描，成本12

 

方式二的改良

select * from students s1 
where not exists (select 1 from students s2 where s1.age<s2.age);

 两次全表扫描，成本8

 

方式三，使用窗口函数

select ＊ from (select students.*,max(age)over() oldest from students) 
where age=oldest;

 一次全表扫描，成本3

 

我本人也有些意料之外，没想到窗口函数威力巨大。

总结：

优先使用窗口函数，然后才是聚集函数；能用exists，就别用in；尽量别做笛卡尔集。