查找至少连续出现三次的所有数字/连续3天的日期【LeetCode】

最新推荐文章于 2024-05-10 18:01:41 发布

weixin_30539625

最新推荐文章于 2024-05-10 18:01:41 发布

阅读量407

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/gered/p/9262670.html

版权

编写一个SQL查询，查找至少连续出现三次的所有数字。
+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

sql server优质解法：

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

连续出现的日期：

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(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,'201808010')

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

mysql办法解决连续时间（8.0以前，8.0以后可以用上述办法）

SELECT userid, max(days) as 'lianxu_days' , min(login_day) as 'start_date' ,max(login_day) as 'end_date'
FROM (SELECT userid,
@cont_day :=
   (CASE
   WHEN (@last_userid = userid AND DATEDIFF(login_dt, @last_dt) = 1) THEN
    (@cont_day + 1)
   WHEN (@last_userid = userid AND DATEDIFF(login_dt, @last_dt) < 1) THEN
    (@cont_day + 0)
    ELSE
    1
     END) AS 'days',
(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,
@last_userid := userid,
@last_dt := login_dt login_day
FROM (SELECT userid , DATE(`Time`) AS 'login_dt'
FROM t_log_onlineinfo where type=0
ORDER BY userid, `Time`) AS t,
(SELECT @last_userid := '',
@last_dt := '',
@cont_ix := 0,
@cont_day := 0) AS t1) AS t2
GROUP BY userid, cont_ix;

编写一个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;