mysql至少连续出现3次的数字_查找至少连续出现三次的所有数字/连续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

(

idINT IDENTITY(1,1),

valINT)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

) SGROUP BY val,idx

【1.2】连续出现的日期：(比如，想要查询连续登录超过3天的用户)

CREATE TABLE#b

(

idINT IDENTITY(1,1),

useridINT,

login_timedatetime)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#bORDER BYuserid ,login_time

--解答SELECT userid, MIN(login_time) AS StartDate, MAX(login_time) AS EndDate, COUNT(1) ASDayCountFROM(SELECTuserid

,login_time

,DATEADD(dd, -ROW_NUMBER() OVER ( PARTITION BY userid ORDER BY login_time), login_time) ASGrpFROM#b

)ASTGROUP BY userid, [Grp]

ORDER BY 1

【2】mysql办法解决

【2.1】连续时间(比如，想要查询连续登录超过3天的用户)

(8.0以前，8.0以后可以用上述sql server 办法)

测试代码

--测试数据代码

CREATE TABLEb

(

idINT primary keyauto_increment,

useridINT,

login_timedatetime);--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) asday_countfrom(select b.*,

date_add(login_time,interval-if(@group_str=userid,@num:=@num+1,@num:=1) day) aslogin ,@group_str:=userid as temp

from b cross join (select @num:=0,@group_str=-1) torder byb.userid,login_time

) tgroup 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;