n卡录双屏出现问题_mysql:连续出现N次类问题

一、问题

下面是某班级学生的某课程的成绩表（table name :sscore,col_num: stu_num(学号），score:成绩），使用sql查找所有至少连续出现3次的成绩：

二、准备

建表

 CREATE TABLE Sscore (
    stu_num VARCHAR(10),
    score DECIMAL(18 , 1 )
);

插入数据

insert into Sscore values('01' , 80);
insert into Sscore values('02' , 70);
insert into Sscore values('03' , 78);
insert into Sscore values('04' , 78);
insert into Sscore values('05' , 78);
insert into Sscore values('06' , 90);
insert into Sscore values('07' , 60);
insert into Sscore values('08' , 71);
insert into Sscore values('09' , 66);

修改一下

alter table sscore modify stu_num varchar(10) primary key;

解法1：关于连续出现N次类问题，自连接解法；

SELECT DISTINCT
    a.score AS continue_3
FROM
    sscore AS a,
    sscore AS b,
    sscore AS c
WHERE
    a.stu_num = b.stu_num - 1
        AND b.stu_num = c.stu_num - 1
        AND a.score = b.score
        AND b.score = c.score;

[解题思路]

1.怎么理解连续出现3次？

即三位同学的学号为依次递增系列。如04,05,06。

2.连续出现3次说明这3为同学的3个成绩相等。

3.使用三张相同的表自连接可以将判断三列相等的问题转化为同一行3个值相等的问题。

自连接写法：

select *
from sscore as a,
   Sscore as b,
   Sscore as c;

这里如果不指定连接三表的主键，那么它们使用全连接。

确定连接三张表的主键

学号依次递增，所以可以用a.学号 = b.学号 - 1 and b.学号 = c.学号-1。

3位同学成绩相等。a.成绩 = b.成绩 and b.成绩 = c.成绩 。

如下图所示：

解法2：

1.我们需要使用row_number()来对stu_num进行排序，sql语句如下：

SELECT 
	stu_num,
	score,
	row_number() over (ORDER BY  stu_num) AS orde
	FROM sscore;

实现如下：

2.我们使用partition by score对score分组（也就是score相同的作为一组），然后再用order by对相同的score组里面的stu_num进行排序，sql语句如下：

SELECT
	stu_num,
	score,
	row_number() over (PARTITION BY score ORDER BY stu_num) AS orde
	FROM sscore;

实现如下：

3.最终代码

SELECT score,count(*) num_count FROM(
		SELECT stu_num,score,
			row_number() over (ORDER BY stu_num)-row_number() over (PARTITION by score order by stu_num) as orde
			from sscore
	)as w GROUP BY score,orde
    having num_count =3;

实现如下：

解法3：利用变量

1. 首先假设变量pre为前一位同学的成绩，默认值为null，变量cnt为重复出现某个成绩的数目，默认为0。
2. 取第一行，a.成绩 = 当前行成绩 ----> 判断与pre是否相等，如果相等，则cnt = cnt+1,
3. 若不相等，则cnt = 1， ----> pre = a.成绩。
4. 遍历到下一行（即第2行）,重复步骤2，步骤2中的取第1行变为取读2行......
5. 最后判断cnt大于等于3的成绩；并去重。

select distinct  dd.score ConsecutiveNums
from  (
select d.score,
       @n :=if(@pre=score,@n+1,@n:=1) count,
       @pre:=score
from sscore d,
     (select @pre:=null, @n :=1) r ) dd
where dd.count>=3;

实现同上。

第三种方法拆解：

SELECT 
    d.score,
    @n:=IF(@pre = score, @n + 1, @n:=1) count,
    @pre:=score
FROM
    sscore d,
    (SELECT @pre:=NULL, @n:=1) r;

实现如下：

三、例题

再来具有实际意义的例题

问题：统计连续登陆的三天数和以上的用户以及他们的首次登陆和最后登陆时间

模拟建表：

CREATE TABLE test1 (
    uid VARCHAR(20) PRIMARY KEY,
    dt DATETIME
);

修改一下：

alter table test1 modify dt date not null;
alter table test1 drop primary key;

插入数据：

insert into test1 (uid,dt) values("guid01","2018/2/28");
insert into test1 (uid,dt) values("guid01","2018/3/01");
insert into test1 (uid,dt) values("guid01","2018/3/02");
insert into test1 (uid,dt) values("guid01","2018/3/04");
insert into test1( uid,dt) values("guid01","2018/3/05");
insert into test1 (uid,dt) values("guid01","2018/3/06");
insert into test1 (uid,dt) values("guid01","2018/3/07");
insert into test1 (uid,dt) values("guid02","2018/3/01");
insert into test1 (uid,dt) values("guid02","2018/3/02");
insert into test1 (uid,dt) values("guid02","2018/3/03");
insert into test1 (uid,dt) values("guid02","2018/3/06");

通过窗口函数排个序：

select uid,dt,row_number() over(partition by uid order by dt) r_n from test1;

实现如下：

第二:

select uid,dt,date_sub(dt,interval r_n day) dis from (
select uid,dt,row_number() over(partition by uid order by dt) r_n from test1) t1;

实现如下：

这里使用函数date_sub()是想实现一个时间的连续，前提是做好第一步的排序，如果“dis”是相同的话，那么对应时间字段“dt”的时间区间就是连续的；

解释一下函数date_sub用法，结合着理解上面：

DATE_SUB() 函数从日期减去指定的时间间隔。

DATE_SUB(date,INTERVAL expr type)

date参数是合法的日期表达式。expr 参数是你希望添加的时间间隔。

type值
MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH

最终代码：

select uid,min(dt) as start_date,max(dt) as end_date,count(1) as counts from(
select uid,dt,date_sub(dt,interval r_n day) dis from (
select uid,dt,row_number() over(partition by uid order by dt) r_n from test1) t1
) t2
group by uid,dis 
having counts>=3;

实现如下：

因为不同的用户上线的时间重复在现实中是必然的，所以根据业务要求筛选出连续三天或以上的用户就得根据实际情况做分组筛选了

本人接触SQL并不是很长，接触的实际业务也不是很多，以上是对sql学习过程中这一类的问题总结，思路呈现以及实现。<抱拳，抱拳，抱拳>