sql 连续三天登陆_leetcode题库-sql练习精讲系列--四、连续出现N次类问题

这是一个系列文章，这个系列的理念是通过一道题，搞懂一类题。涵盖了SQL面试最常考的知识点。搞懂这些题，面试时工作中sql不可能有问题。

文章分为引入问题-完整解析-答案-leetcode题和答案-知识点拓展-BAT等大厂面试真题几个部分。

希望能帮你全方位的弄懂。有问题可以留言，码字不易，写一篇要好几个小时，希望能得到点赞收藏哦。

一、问题

下面是某班级学生的某课程的成绩表（表名Sscore，列名：学号、成绩），使用SQL查找所有至少连续出现3次的成绩。

二、准备

create table Sscore
(`学号` varchar(10),
`成绩` 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);

三、解答

[解题思路]

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.成绩 。

[代码]

select distinct a.成绩 as 连续出现3次的成绩
from Sscore as a,
   Sscore as b,
   Sscore as c;
 where a.学号 = b.学号 - 1
   and b.学号 = c.学号 - 1
   and a.成绩 = b.成绩
   and b.成绩 = c.成绩;

[leetcode题库问题-180. 连续出现的数字]

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

[解题思路]

1.使用变量。

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

[代码]

SELECT DISTINCT t.num, MAX(cnt)
FROM 
            (SELECT a.num,
                    @cnt := if(@pre = a.num, @cnt+1, 1) cnt,  -- 最后的cnt，pre起到命名的作用
                    @pre := a.num pre
            FROM (SELECT * FROM logs ORDER BY num) a, -- 注意这里的order排序，根据题意决定是否加上。区别在需不需要重新排列。
                     (SELECT @pre = null,
                             @cnt = 0
                                     ) b
         ) t
WHERE t.cnt >= 3
GROUP BY t.num;

2.窗口函数

SELECT num, max(num_rk) AS num_cnt 
FROM 
    (SELECT num, row_number()over(PARTITION BY num) AS num_rk   -- 先计算出排名
    FROM logs) t
GROUP BY t.num   -- 根据最大的排名来知道num的出现次数
HAVING num_cnt >= 3; -- 用排名进行筛选

[知识点讲解]

[变量分类]

• 用户自定义变量
• 局部变量
• 会话变量
• 系统变量
• 会话变量
• 全局变量

这里主要讲用户自定义变量。

[什么是用户自定义变量]

用户自定义变量是一个用来存储内容的临时容器，在连接MySQL的整个过程中都存在，可以使用下面的SET和SELECT语句来定义它们：

mysql> SET @one := 1;
mysql> SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor);
mysql> SET @last_week := CURRENT_DATE - INTERVAL 1 WEEK;

[变量使用场景]

• 作为循环计数器来计算循环执行的次数。
• 保持要通过控制流语句(如WHILE)进行测试的值。
• 存储函数或存储过程返回的值。

[书写模板拆解]

1. 变量存放位置

# 生成变量 pre, cnt 并默认为 null,0.    
select @pre := null,@cnt := 0 

1. 迭代写法

# 某一次的迭代写法.    
@cnt := if(@pre = a.Num, @cnt + 1, 1) cnt, 
-- 先判断上一行的num和当前行的num是否一样，如果一样，说明上下行的数字一样则变量@cnt+1，如果不一样，则cnt还是为1.    

@pre := a.Num pre 
-- 遍历，将当前行的num赋值给pre，然后进入判断下一行

四、大厂面试真题

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

select
 uid,min(dt),max(dt),count(1) as counts
 from
    (
    select
    uid ,dt, date_sub(dt,rn) as dis
    from
        (
        select
        uid ,dt,row_number()over (partition by uid order by dt)rn
        from continuous
        )t1
    )t2
group by uid ,dis 
having counts>2