MYSQL 连续性问题求解

MYSQL 连续性问题求解

问题：
使用SQL查找学生的成绩表（表名；grade，列名；序号，成绩）中至少连续出现3次的成绩。

create table grade(
num smallint not null,
score int(3) not null);

insert into grade values
(1,89),
(2,76),
(3,76),
(4,84),
(5,84),
(6,84),
(7,76),
(8,76),
(9,76);

方法一：使用多个自连表来实现
不足：当查找的连续值过多时，运行效率过低

select distinct(a.score) from grade as a, grade as b, grade as c
where a.score = b.score and b.score = c.score 
and a.num = b.num + 1 and b.num = c.num+ 1
;

方法二：构造一个等差数列，利用差值来查找
使用的技巧：@rn声名变量，@rn := @rn + 1 构造一个等差数列，（select @rn := 10）as a ,给声名的变量一个初始值，为10
as a,是因为必须要给该临时表一个别名

select score from
(
select num, score, @rn := @rn + 1 as nump from grade,
(select @rn := 10) as a
order by score
) as b
group by (b.num - b.nump), score
having count(b.num - b.nump) > 2
;

扩展：
查找下表中连续两年夺冠的球队，及开始和结束的时间
CREATE TABLE nba(team varchar(20), year int); # 建表语句
INSERT nba(team ,year ) VALUES (‘活塞’,1990), (‘公牛’,1991),(‘公牛’,1992),(‘公牛’,1993),(‘火箭’,1994),
(‘火箭’,1995),(‘公牛’,1996),(‘公牛’,1997),(‘公牛’,1998),(‘马刺’,1999),(‘湖人’,2000),(‘湖人’,2001),
(‘湖人’,2002),(‘马刺’,2003),(‘活塞’,2004),(‘马刺’,2005),(‘热火’,2006),(‘马刺’,2007),(‘凯尔特人’,2008),
(‘湖人’,2009),(‘湖人’,2010);

方法一：构造等差数列，计数（等差数据与year列的**差值个数**）
优点：可以找出连续时间的开始和结束时间点，只要是连续一段时间，只显示一条记录
缺点：需要理解定义变量，以及最后计算的是差值的个数（count(y_cha)）
select team, min(year), max(year) from 
(
select team, year, @rn := @rn + 1, (year - @rn) as y_cha 
from (select @rn := 1990) as argu, nba
order by team
) as t
group by team, y_cha
having count(y_cha) > 1; 


方法二：使用lead() over()函数
优点：相比上面简单，只要理解lead()窗口函数即可
缺点：连续登录天数限制太死，如果连续一个月登录，会显示多行记录，可以按需要去重
select team, year from 
(
select team, year, LEAD(year,2) over(partition by team ORDER BY year) year_lead_2
from  nba
order by team
) as t
WHERE year_lead_2 - year =2