select sno,sname,ssex,row_number() over(order by sno desc ) 排名 from student;
select sno,sname,ssex,row_number() over(order by sno desc ) 排名 from student where sno = ‘109’;
2、row_number() over (partition by order by ) 结合使用
select sno,sname,ssex,sclass,row_number() over(partition by sclass order by sno desc ) rank from student;
3、 RANK()
rank 返回数据项在分组中的排名,排名相等会在名次中留下空位:
LAG:
lag ,lead 分别是向前,向后;
lag 和lead 有三个参数,第一个参数是列名,第二个参数是偏移的offset,第三个参数是 超出记录窗口时的默认值
LAG(column_name1,1) OVER(ORDER BY column_name2)
LEAG(column_name1,1) OVER(ORDER BY column_name2)
备注:超出记录窗口时的默认值其实就是自己定义数字,填充。
select sno,sname,ssex,lag(sclass,1,222)over(order by sclass desc) from student
没加记录超出默认值时的参数:如下
以上sclass括号内的1 加不加都是可以执行通的。
select sno,
sname,
ssex,
lead (sclass, 1) over(order by sclass desc) 对应班级号
from student order by sno desc;