mysql 开窗函数

Dianthe ·

更新时间:2024-09-20

· 640 次阅读

场景1：取max

表结构

create table tb_test_info(
	student_id char(4) comment '学号',
	grade_id int comment '课程号',
	score int comment '分数',
	primary key (student_id,grade_id)
);
insert into tb_test_info(student_id,grade_id,score)values('001',1,97);
insert into tb_test_info(student_id,grade_id,score)values('001',2,50);
insert into tb_test_info(student_id,grade_id,score)values('001',3,70);
insert into tb_test_info(student_id,grade_id,score)values('002',1,92);
insert into tb_test_info(student_id,grade_id,score)values('002',2,80);
insert into tb_test_info(student_id,grade_id,score)values('002',3,30);
insert into tb_test_info(student_id,grade_id,score)values('003',1,93);
insert into tb_test_info(student_id,grade_id,score)values('003',2,95);
insert into tb_test_info(student_id,grade_id,score)values('003',3,85);
insert into tb_test_info(student_id,grade_id,score)values('004',1,73);
insert into tb_test_info(student_id,grade_id,score)values('004',2,78);
insert into tb_test_info(student_id,grade_id,score)values('004',3,87);

需求：查询每门课程的前2名成绩

不分组的排序

SELECT
	a.*,
	@rank := @rank +1 as rank
FROM
	tb_test_info a ,( SELECT @rank := 0) b 
order by a.grade_id, a.score desc

在这里插入图片描述
但是这个并不是我们想要的结果，我们想要的结果是按照分组排序。

分组排序
注意，我们可以使用group by分组后的结果来作为中间值来计算,但是我们只能计算一个最值

select t.*,s.max_score from tb_test_info t ,(select  grade_id ,max(score) max_score from tb_test_info  group by grade_id) s
where 
t.grade_id = s.grade_id
AND T.score = S.MAX_SCORE

使用多个变量

SELECT
	a.*,
	@pre_grade as 'pre_row_grade_id',
	/**
		由于已经按照 grade_id 进行了排序，
    	- when 当前行的grade_id != 之前行grade，说明进行到了下一个grade分组，需要重新计数排序， 所以将rank 设置为1
    	- when 当前行的grade_id == 之前行grade, 则rank 自增1
    **/
  IF( @pre_grade = a.grade_id, @rank := @rank + 1, @rank := 1 ) AS rank,
	@pre_grade := a.grade_id as 'thiz_row_grade_id'
FROM
	tb_test_info a ,( SELECT @rank := 0, @pre_grade := NULL ) b 
ORDER BY
	a.grade_id ASC,
	a.score DESC

在这里插入图片描述

如果 group by 多个字段，上述的逻辑需要调整。

最终结果

select * from (
SELECT
	a.*,
IF
	( @pre_grade = a.grade_id, @rank := @rank + 1, @rank := 1 ) AS rank,
	@pre_grade := a.grade_id AS 'pre_grade_id' 
FROM
	tb_test_info a ,( SELECT @rank := 0, @pre_grade := NULL ) b 
ORDER BY
	a.grade_id ASC,
	a.score DESC
) tt where rank <=2

场景2： sum()

引用： https://blog.csdn.net/qq_34325438/article/details/87883813
表结构

create table income_tl(
	user_id int comment '用户id',
	create_date date  COMMENT'经营日期',
	income int COMMENT'日营收'
);
insert into income_tl values(1,'2016-03-01',100);
insert into income_tl values(1,'2016-03-02',300);
insert into income_tl values(1,'2016-03-03',200);
insert into income_tl values(1,'2016-03-04',500);
insert into income_tl values(1,'2016-03-05',500);
insert into income_tl values(2,'2016-03-01',200);
insert into income_tl values(2,'2016-03-01',300);
insert into income_tl values(2,'2016-03-03',300);
insert into income_tl values(2,'2016-03-04',500);
insert into income_tl values(2,'2016-03-05',400);

需求：计算每个用户的当日累进营收。

最终结果

SELECT
	a.*,
IF
	( @pre_user_id = a.user_id, @sum_income := @sum_income + a.income, @sum_income := a.income ) AS sum_income,
	@pre_user_id := a.user_id 
FROM
	income_tl a,
	( SELECT @pre_user_id := NULL, @sum_income := 0 ) b;

在这里插入图片描述