查找至少连续出现三次的所有数字/连续3天的日期【LeetCode】
编写一个SQL查询，查找至少连续出现三次的所有数字。
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
【1】sql server优质解法：
【1.1】连续3次以上出现的数字
CREATE TABLE #A ( id INT IDENTITY(1,1), val INT ) INSERT INTO #A(val) VALUES(1),(1),(1),(1),(2),(2),(3),(4),(4),(4),(4),(4) INSERT INTO #A(val) VALUES(1)
select * from #A
SELECT val,MIN(id) AS minid,MAX(id) AS maxid, COUNT(1) AS cmd FROM ( SELECT *,id-ROW_NUMBER() OVER( PARTITION BY val ORDER BY id ) AS idx FROM #A ) S GROUP BY val,idx
【1.2】连续出现的日期：（比如，想要查询连续登录超过3天的用户）
CREATE TABLE #b ( id INT IDENTITY(1,1), userid INT, login_time datetime ) INSERT INTO #b(userid,login_time)VALUES(101,'20180801'),(102,'20180801') INSERT INTO #b(userid,login_time)VALUES(102,'20180802') INSERT INTO #b(userid,login_time)VALUES(101,'20180803'),(102,'20180803') INSERT INTO #b(userid,login_time)VALUES(101,'20180804'),(102,'20180804') INSERT INTO #b(userid,login_time)VALUES(101,'20180805'),(102,'20180805') INSERT INTO #b(userid,login_time)VALUES(101,'20180806') INSERT INTO #b(userid,login_time)VALUES(101,'20180807')
INSERT INTO #b(userid,login_time)VALUES(101,'20180809')
SELECT * FROM #b ORDER BY userid ,login_time --解答 SELECT userid, MIN(login_time) AS StartDate, MAX(login_time) AS EndDate, COUNT(1) AS DayCount FROM ( SELECT userid ,login_time ,DATEADD(dd, -ROW_NUMBER() OVER ( PARTITION BY userid ORDER BY login_time), login_time) AS Grp FROM #b ) AS T GROUP BY userid, [Grp] ORDER BY 1
【2】mysql办法解决
【2.1】连续时间（比如，想要查询连续登录超过3天的用户）
（8.0以前，8.0以后可以用上述sql server 办法）
测试代码
-- 测试数据代码 CREATE TABLE b ( id INT primary key auto_increment, userid INT, login_time datetime ); -- select * from b order by userid; INSERT INTO b(userid,login_time) VALUES(101,'20180801'),(102,'20180801'); INSERT INTO b(userid,login_time) VALUES(101,'20180802'),(102,'20180802'); INSERT INTO b(userid,login_time) VALUES(101,'20180803'),(102,'20180803'); INSERT INTO b(userid,login_time) VALUES(101,'20180804'),(102,'20180804'); INSERT INTO b(userid,login_time) VALUES(101,'20180805'),(102,'20180805'); INSERT INTO b(userid,login_time) VALUES(101,'20180806'); INSERT INTO b(userid,login_time) VALUES(101,'20180807'); INSERT INTO b(userid,login_time) VALUES(101,'20180808'); INSERT INTO b(userid,login_time) VALUES(101,'20180809'); INSERT INTO b(userid,login_time) VALUES(101,'20180810'); INSERT INTO b(userid,login_time) VALUES(101,'20180731'); INSERT INTO b(userid,login_time) VALUES(102,'20180731');
插入后生成的测试表数据：
实现代码：
select userid,min(login_time) min_date,max(login_time) max_date,count(1) as day_count from ( select b.*, date_add(login_time,interval -if(@group_str=userid,@num:=@num+1,@num:=1) day) as login , @group_str:=userid as temp from b cross join (select @num:=0,@group_str=-1) t order by b.userid,login_time ) t group by userid,login
结果：
【2.2】连续3次以上出现的数字
强烈推荐解法三
编写一个SQL查询，查找至少连续出现三次的所有数字。
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
-- 建表 create table Logs(id int primary key auto_increment,num int);
-- 造数据
INSERT INTO Logs(num) VALUES(1),(1),(1),(1),(2),(2),(3),(4),(4),(4),(4),(4),(1);
mysql解法：
这道题给了我们一个Logs表，让我们找Num列中连续出现相同数字三次的数字，那么由于需要找三次相同数字，所以我们需要建立三个表的实例.
我们可以用l1分别和l2, l3内交，l1和l2的Id下一个位置比，l1和l3的下两个位置比，然后将Num都相同的数字返回即可：
解法一:
SELECT DISTINCT l1.Num FROM Logs l1 JOIN Logs l2 ON l1.Id = l2.Id - 1 JOIN Logs l3 ON l1.Id = l3.Id - 2 WHERE l1.Num = l2.Num AND l2.Num = l3.Num;
下面这种方法没用用到Join，而是直接在三个表的实例中查找，然后把四个条件限定上，就可以返回正确结果了：
解法二:
SELECT DISTINCT l1.Num 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;
再来看一种画风截然不同的方法，用到了变量count和pre，分别初始化为0和-1，然后需要注意的是用到了IF语句，MySQL里的IF语句和我们所熟知的其他语言的if不太一样，相当于我们所熟悉的三元操作符a?b:c，若a真返回b，否则返回c，具体可看这个帖子。那么我们先来看对于Num列的第一个数字1，pre由于初始化是-1，和当前Num不同，所以此时count赋1，此时给pre赋为1，然后Num列的第二个1进来，此时的pre和Num相同了，count自增1，到Num列的第三个1进来，count增加到了3，此时满足了where条件，t.n >= 3，所以1就被select出来了，以此类推遍历完整个Num就可以得到最终结果：
解法三：
SELECT DISTINCT Num FROM ( SELECT Num, @count := IF(@pre = Num, @count + 1, 1) AS n, @pre := Num FROM Logs, (SELECT @count := 0, @pre := -1) AS init ) AS t WHERE t.n >= 3;
【3】求出某段时间内玩家最大连胜次数
【3.1】思路分解
-- 获取某段日期内某个表的玩家最大连胜次数 -- t1 构造全局顺序伪列 all_rn use log_db; with t1 as ( select numid,result,logtime,row_number() over(order by numid,logtime) as all_rn from log_ZhuoLaoMaZi_20255_2021_07 order by numid,logtime limit 100 --时间段过滤，在这里添加条件 ) -- t2 筛选出获胜局 , result >0 为赢 ，这时如果不连续胜利的，那么 all_rn 必定会断连续 ,t2 as ( select * from t1 where result >0 ) -- t3 构造根据上面的结果集，以用户 numberid 分组 构造分组顺序伪列 group_rn ,t3 as ( select *, row_number() over(partition by numid order by logtime) as group_rn from t2 ) -- t4 构造出可以分组的列，连续获胜的构造出同一个值出来，以便分组获取连胜次数 ,t4 as ( select *, all_rn - group_rn as group_val from t3 ) -- 最终结果 ,t5 as ( select numid as '玩家id',group_val '分组辅助列', min(logtime) as '连胜begintime' ,max(logtime) as '连胜endtime',count(1) as '连胜数' from t4 group by numid,group_val ) select * from t5;
抽取了一百条做查询测试，如上图，只有一行是0的；所以应该有99行相关连胜，最终查询结果如下：
下图中连胜数加起来确实是 99行，所以是正确的，想要获取最大连胜数的行信息，只需要再把代码 order by 连胜数 desc limit 1 即可；
【3.2】最佳实践
本小节的思路、代码来自：SQLSERVER技术交流群
use tempdb; if object_id('#temp1') is not null drop table #temp1 create table #temp1(userid int ,result int,logtime datetime) insert into #temp1 values(101,2,'2021-08-31 08:25:25'),(101,2,'2021-08-31 08:25:27'),(101,2,'2021-08-31 08:25:29') insert into #temp1 values(101,2,'2021-08-31 08:25:31'),(101,2,'2021-08-31 08:26:01'),(101,1,'2021-08-31 08:26:03') insert into #temp1 values(101,2,'2021-08-31 08:26:05'),(101,2,'2021-08-31 08:26:06'),(101,2,'2021-08-31 08:26:07') insert into #temp1 values(101,2,'2021-08-31 08:27:05'),(101,2,'2021-08-31 08:28:06'),(101,2,'2021-08-31 08:29:07') insert into #temp1 values(101,2,'2021-08-31 09:27:05'),(101,2,'2021-08-31 10:28:06'),(101,2,'2021-08-31 11:29:07') insert into #temp1 values(101,2,'2021-09-11 08:25:25'),(101,2,'2021-09-11 08:25:27'),(101,2,'2021-09-12 08:25:29') insert into #temp1 values(101,2,'2021-09-13 08:25:31'),(101,2,'2021-09-14 08:26:01'),(101,2,'2021-09-14 08:26:03') insert into #temp1 values(101,2,'2021-09-15 08:26:05'),(101,2,'2021-09-16 08:26:06'),(101,2,'2021-09-17 08:26:07') insert into #temp1 values(101,2,'2021-09-18 08:27:05'),(101,2,'2021-09-19 08:28:06'),(101,1,'2021-09-20 08:29:07') insert into #temp1 values(101,2,'2021-09-21 09:27:05'),(101,2,'2021-09-22 10:28:06'),(101,2,'2021-09-25 11:29:07')
简写一下【3.1】：
select userid,result,all_rn-group_rn as [分组伪列],count(1) count_number from ( select * ,row_number() over(partition by userid,result order by logtime) as all_rn ,row_number() over(partition by userid order by logtime) as group_rn from #temp1 ) y group by userid,result,all_rn-group_rn--having result=2
另外一种思路：
select userid,count(1) count_number ,max(result) rt from ( select * ,sum(rt) over(partition by userid order by logtime) sum_rt from ( select * ,iif(lag(result,1,result) over(partition by userid order by logtime) = result,0,1) as rt from #temp1 ) t ) t1 group by userid,sum_rt --having rt=2
