方式一
SELECT DISTINCT
l1.Num ConsecutiveNums
FROM
LOGS l1
LEFT JOIN LOGS l2 ON l1.Id = l2.Id - 1
LEFT JOIN LOGS l3 ON l1.Id = l3.Id - 2
WHERE
l1.Num = l2.Num AND l2.Num = l3.Num;
编写一个 SQL 查询,查找所有至少连续出现三次的数字。+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
+-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+
如果是在Java等编程语言中,直接用for循环遍历一遍基本就出来了,但是在MySQL没有遍历表这种流程控制结构。
但我我们可以分析一下编程语言中的过程,首先我们需要一个pre变量记录前面一个值是多少,以及前面那个值连续出现次数。如果pre == 当前值,则当前值到现在连续出现的次数n = n + 1(n为pre连续出现次数) 否则当前值连续出现次数n = 1,并且将pre更新为当前值。
在MySQL中的表虽然想编程语言中的数组,但是我们也可以做到类似遍历的效果。
#对结果进行去重处理 select distinct Num as ConsecutiveNums #将Num以及连续出现的次数作为一个新表 from ( #计算Num,到当前遍历的位置连续出现的次数 select Num, ( case #如果Num == 前一个值,个数自增 when @prev = Num then @count := @count + 1 #否则计数器count为1,并更新prev为当前Num的值 when (@prev := Num) is not null then @count := 1 end ) as CNT from Logs, (select @prev := null,@count := null) as t ) as temp #筛选出连续出现次数超过3的 where temp.CNT >= 3
这道题涉及到子查询、case结构、用户变量等,比较复杂,但是整道题的思路还是比较清晰简洁的,主要是代码实现的时候需要注意一下语法。
请参考我的专栏
MySQL从入门到精通之子查询
MySQL从入门到精通之流程控制结构
MySQL从入门到精通之变量
方式一
SELECT DISTINCT
l1.Num ConsecutiveNums
FROM
LOGS l1
LEFT JOIN LOGS l2 ON l1.Id = l2.Id - 1
LEFT JOIN LOGS l3 ON l1.Id = l3.Id - 2
WHERE
l1.Num = l2.Num AND l2.Num = l3.Num;
转载于:https://my.oschina.net/u/3424826/blog/1843750
题目
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
+-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+
思路
三次自连接;
考虑连续多个情况会重复出现Num 所以要加上distinct;解法
SELECT DISTINCT l1.Num AS ConsecutiveNums FROM LOGS l1, LOGS l2, LOGS l3 WHERE l1.Id = l2.Id - 1 AND l2.Id = l3.Id - 1 AND l1.Num = l2.Num AND l2.Num = l3.Num
目录
问题
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
+-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+
构造数据
--oracle create table Logs (id int,num int); insert into logs(id,num) values (1,1); insert into logs(id,num) values (2,1); insert into logs(id,num) values (3,1); insert into logs(id,num) values (4,2); insert into logs(id,num) values (5,1); insert into logs(id,num) values (6,2); insert into logs(id,num) values (7,2);
解题
Oracle
解题思路
select Id, Num, row_number() over (order by Id), row_number() over (partition by Num order by Id), row_number() over (order by Id) - row_number() over (partition by Num order by Id) from Logs;
可观察特点
代码
select distinct Num as consecutiveNums from ( select Num, (row_number() over (order by Id) - row_number() over (partition by Num order by Id)) lag from Logs ) group by Num, lag having count(1) >= 3;
MySQL
解题思路
#①首先遍历一遍整张表,找出每个数字的连续重复次数 #具体方法为: #初始化两个变量,一个为pre,记录上一个数字;一个为count,记录上一个数字已经连续出现的次数。 #然后调用if()函数,如果pre和当前行数字相同,count加1极为连续出现的次数;如果不同,意味着重新开始一个数字,count重新从1开始。 #最后,将当前的Num数字赋值给pre,开始下一行扫描。 select Num, #当前的Num 数字 if(@pre=Num,@count := @count+1,@count := 1) as nums, #判断 和 计数 @pre:=Num #将当前Num赋值给pre from Logs as l , (select @pre:= null,@count:=1) as pc #这里需要别名 #上面这段代码执行结果就是一张三列为Num,count as nums,pre的表。 #②将上面表的结果中,重复次数大于等于3的数字选出,再去重即为连续至少出现三次的数字。 select distinct Num as ConsecutiveNums from (select Num, if(@pre=Num,@count := @count+1,@count := 1) as nums, @pre:=Num from Logs as l , (select @pre:= null,@count:=1) as pc ) as n where nums >=3; #注意:pre初始值最好不要赋值为一个数字,因为不确定赋值的数字是否会出现在测试表中。
代码
# Write your MySQL query statement below select distinct Num as ConsecutiveNums from (select Num, if(@pre=Num,@count := @count+1,@count := 1) as nums, @pre:=Num from Logs as l , (select @pre:= null,@count:=1) as pc ) as n where nums >=3;