MySQL——连续出现的数字

前置知识

MySQL中变量的分类

• 系统变量：系统变量由系统提供，不是用户定义的，属于服务器层面。如查看系统所有变量 show global variables;，分为全局变量和会话变量
  • 全局变量在MySQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改
  • 会话变量在每次建立一个新的连接的时候，由MySQL来初始化。MySQL会将当前所有全局变量的值复制一份。来做为会话变量
• 定义变量：变量是用户自定义的，不是系统的，分为用户变量和局部变量
  • 局部变量一般用于SQL的语句块中，如存储过程中的begin end语句块。其作用域仅限于该语句块内。生命周期也仅限于该存储过程的调用期间
  • 用户变量跟会话变量相似。MySQL中用户变量不用事前申明，在用的时候直接用 @变量名 使用就可以了

局部变量定义语法形式

• DECLARE var_name [, var_name]... data_type [ DEFAULT value ];

用户变量定义语法形式 

• @var_name 形式创建用户定义的变量，并且用户定义的变量不区分大小写
• 名字必须以@开头
• 声明变量的时候需要使用SET语句
• 将值分配给用户定义的变量的方式
  • 使用SET语句，使用:=或=作为SET语句中的赋值运算符：SET @var_name := value; 
  • 使用SELECT语句，使用:=作为语句中的赋值运算符(因为在SELECT语句中，MySQL将=运算符视为相等运算符)：SELECT @var_name := value;在赋值之后，可以使用后续语句中允许表达式的变量

题目

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

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id 是这个表的主键。
============================================================
输入：
Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
输出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
解释：1 是唯一连续出现至少三次的数字

解题

方式一：考虑id的连续的，无中断情况

连续出现的意味着相同数字的 Id 是连着的，则可以使用三次内连接来实现

select distinct l1.num as ConsecutiveNums
from 
    logs l1,
    logs l2,
    logs l3
where l1.id + 1 = l2.id and l2.id + 1 = l3.id and l1.num = l2.num and l2.num = l3.num

方式二：考虑id是不连续的

通过定义两个用户变量进行计数即可

select distinct temp.num as ConsecutiveNums
from (
  select 
    num,
    case
      when @prev = num then @count :=@count + 1
      when (@prev := num) is not null then @count := 1
    end as cnt
  from logs,(select @prev := null,@count := null) as t
) as temp
where temp.cnt >= 3