SQL模拟开窗函数

最近在做SQL题的时候，发现如果可以使用开窗函数的话，这样就大可以大大降低查询语句的复杂化。下面我使用SQL语言来模拟开窗函数生成序号。至于开窗函数的其他功能，请自行推导。

题目

表，table（Pid、Pclass、Pscore），用最高效简单的SQL列出各班成绩前五名的列表，显示学号、班级、成绩名次四个字段
分析：这个题，第一眼看上去使用开窗函数就非常容易的解决。
我认为使用SQL解题的重点在于，如何给出一个序号（成绩名次）
下面讲述，如何使用SQL来模拟开窗函数

相关重点

先回顾一下相关的重点

SQL的执行顺序

(1)from
(3) join
(2) on
(4) where
(5)group by(开始使用select中的别名，后面的语句中都可以使用)
(6) avg,sum…
(7)having
(8) select
(9) distinct
(10) order by
(11) limit
对于这个题，正常的想法是 group by + order by ，但这样会相当麻烦

变量

变量分为用户变量（@）和系统变量（@@）
1、变量的定义

SET @var_name = expr [, @var_name = expr] ...

使用 := 作为分配符，因为 = 会被视为比较操纵符。没有初始化的变量应该赋null
注意：在SELECT语句中，表达式发送到客户端后才进行计算。这说明在HAVING、GROUP BY或者ORDER BY子句中，不能使用包含SELECT列表中所设的变量的表达式。即在嵌套的查询中，变量应该单独包含在一个SELECT语句中。

case…when语句

case-when then else end 语句.
例：

case sex
  when '1' then '男'
  when '2' then '女’
  else '其他' end

与java中swich case用法基本相同。

if语句

IF(sex = '1','男','女')

相对于java的if，大大简化的if语句的形式。

解题

直接上我的代码

解法一 基于case

create table student01(
	Pid int,
	Pclass int,
	Pscore int
);
insert into student01 values(1,1,90);
insert into student01 values(2,1,70);
insert into student01 values(3,1,60);

insert into student01 values(1,2,86);
insert into student01 values(2,2,64);
insert into student01 values(3,2,73);

insert into student01 values(1,3,66);
insert into student01 values(2,3,59);
insert into student01 values(3,3,84);

select * from (
select it.Pid, 
       it.Pclass, 
       it.Pscore,
       case 
          when @preVal = it.Pclass then @curVal := @curVal + 1
          when @preVal := it.Pclass then @curVal := 1
       end AS no_income
from (select * from student01 order by Pclass) it, (select @preVal:='', @curVal:=0) r
order by it.Pclass asc, it.Pid asc) as a where a.no_income<3;

代码讲解：case when 语句是保证在第一次读取Pclass时将Pclass的值赋给@preVal变量，第二次的话直接就是直接执行case的条件一，产生递增的序号。如果是不同的Pclass就将执行条件二、重写@preVal。

解法二 基于if

SELECT it.Pid,  
       it.Pscore,
   IF(@DEPTNO = it.Pclass, @RN := @RN + 1, @RN := 1) AS ROW_NUMBER ,
    @DEPTNO :=  it.Pclass AS class
FROM (select Pid,Pclass,Pscore from student01 order by Pid) it, (SELECT @DEPTNO := '', @RN := 0) C
ORDER BY class;

代码讲解：必须要将变量@DEPTNO 的赋值放到if语句之后，这样才能保证if语句中的判断条件为当前的记录Pclass与上一条记录的Pclass，相同则增加1，不同则赋1.