在现实生活中,下面的情况是不是经常发生:
在每次期末考试中,规定每个班级成绩最高(即第一名)的学生将获得三好学生的荣誉,那么怎样取出成绩最高学生的学号、姓名、成绩等相关信息呢?
为了便于说明问题,先把表的一些相关信息予以介绍:
现有如下的表结构: 最后的结果应该为:
sid sname sscore sclass sid sscore sclass
---- --------- ------ ------ ---- ------ ------
1 zhang san 90 class1 1 90 class1
2 li si 85 class1 3 90 class1
3 zhang san 90 class1 4 100 class2
4 zhang san 100 class2 7 null class3
5 li si 90 class2 8 null class3
6 zhang san 90 class2
7 zhang san null class3
8 zhang san null class3
其中sid为主键,现在我想首先取出sname为zhang san的数据,然后按照sclass进行分组,最后取出sscore最大的所对应的sid和sclass等相关信息(tips:所有字段中除了sscore是number型之外,其余全为varchar型,另外之所以要把数据凑成这样是为了测试重复数据)。
下面首先给出正确的解答:
1 select e.sid,e.sname,max(e.sscore) over (partition by sclass) sscore
2 from (
3 select sid,sname,sclass,sscore,
4 rank() over(partition by sclass order by sscore desc) as rn
5 from chunting.dbo.student_score
6 where sname='zhang san'
7 ) e
8 where e.rn=1
(以上语句在SQL Server 2005中调试通过)
也许一般人当看到这个问题的第一反应便是用group by来实现,这样就掉进了错误的陷阱中,因为由于在sql语句中如果使用了group by那么sid字段就无法直接取出,因为它既不是group by字段,也不能被聚合函数所修饰,所以只能用partition by(以后如果遇到同样的问题即:既要取分组后的最大值,又要取像sid这样的字段,就可以仿照上面的写法来解决,这一招很是实用,希切记!)。如果在数据库中没有重复的数据那上面所说的就可以解决问题了,但在现实中往往是相反的,例如在一个班级中同时有多人考取第一名的情况时有发生,这就要采取下面的方法,即利用rank() over(partition by sclass order by sscore desc) as rn它的意思就是首先根据sclass进行分组然后根据sscore进行排序并且赋值以"序号"(rank的好处在于它把sscore值相同的行赋以相同的"序号",这就为以后把所有第一名都取出来提供了保证,这也是它与row_number的最大区别,row_numer是根据分组然后给每行赋以不同的"序号"),为了便于说明情况下面予以展示这两种语句的运行结果(即上面sql语句的3456行):
3456的运行结果为: 把rank改为row_number其余不变(3456)的结果为:
sid sname sclass sscore rn sid sname sclass sscore rn
--- -------- ------ ------ -- --- -------- ------ ------ ---
1 zhang san class1 90 1 1 zhang san class1 90 1
3 zhang san class1 90 1 3 zhang san class1 90 2
4 zhang san class2 100 1 4 zhang san class2 100 1
6 zhang san class2 90 2 6 zhang san class2 90 2
7 zhang san class3 null 1 7 zhang san class3 null 1
8 zhang san class3 null 1 8 zhang san class3 null 2