开窗函数功能很强大
Oracle数据库是支持这种写法的,mysql的话要8版本或以上才支持
PARTITION 中文是分割的意思,ORDER 是排序的意思,把一组数据按照制定的字段进行分割成各种组,然后组内按照某个字段排序
简而言之就是
分组后对组内数据排序
写法:
select
id
,name,class_id,score,lag(score,
1
,
0
)
over (partition by class_id order by score desc) before_score
from
t_student;
mysql8.0之前写法:
SELECT
c.id,
c.NAME,
c.class_id,
c.score,
c.before_score
FROM
(
SELECT
IF ( @id = a.class_id, @lagname := @score, @lagname := '' ) AS before_score,
@id := a.class_id AS aclass_id,
@score := a.score AS aafter_score,
a.*
FROM
( SELECT * FROM t_student ORDER BY class_id, score DESC ) a,
( SELECT @lagname := NULL, @id := 0, @score := NULL ) b
) c;
或者,采用子查询,先排序,再分组,找到最值元素
扩展
over (partition by order by)
还可以有这种写法
over (order by),整个数据就是一个大块
配合ROW_NUMBER() 函数可以做到组内数据从1开始编号
ROW_NUMBER() over (partition by class_id order by score desc) no 对数据的编号
与OVER(PARTITION BY... ORDER BY...)匹配使用的函数
row_number() over()、rank() over()和dense_rank() over()
在上面的例子里,使用ROW_NUMBER()可以对数据编号,但是有一个问题,例子中的MI_ID是不可以重复的,如果在可以重复的情况下,就有并列的情况,这样就无法取出并列的数据,只能取单一排序的数据。所以这里可以换成 rank() over()和dense_rank() over()来进行编号:(rank() over()和dense_rank() over()的区别如图)
sum() over(),first_value() over()和last_value() over()的使用
sum() over()分组求和
first_value() over()求分组第一条
last_value() over()求分组最后一条
其中用row_number() over()取编号第一条的也可以实现first_value() over()的效果