SQL implements LeetCode (180. Continuous numbers)

[LeetCode] 180. Consecutive Numbers

Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

This question gives us a Logs table and asks us to find the numbers that have the same number appearing three times in a row in the Num column. Since we need to find the same number three times, we need to create three table instances. We can use l1 to intersect with l2 and l3 respectively, compare the next position of l1 and l2's Id, and compare the next two positions of l1 and l3, and then return the numbers with the same Num:

Solution 1:

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;

The following method does not use Join, but directly searches in the instances of the three tables, and then limits the four conditions to return the correct result:

Solution 2:

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;

Let's look at a completely different method. The variables count and pre are used and initialized to 0 and -1 respectively. Then, it should be noted that the IF statement is used. The IF statement in MySQL is different from the if statement in other languages ​​we are familiar with. It is equivalent to the familiar ternary operator a?b:c. If a is true, it returns b, otherwise it returns c. Then let's first look at the first number 1 in the Num column. Because pre is initialized to -1, which is different from the current Num, count is assigned 1 at this time, and pre is assigned 1 at this time. Then the second 1 in the Num column comes in, and pre is the same as Num at this time. Count increases by 1. When the third 1 in the Num column comes in, count increases to 3. At this time, the where condition is met, tn >= 3, so 1 is selected. And so on, traversing the entire Num to get the final result:

Solution 3:

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 tn >= 3;

References:

https://leetcode.com/discuss/54463/simple-solution

https://leetcode.com/discuss/87854/simple-sql-with-join-1484-ms

https://leetcode.com/discuss/69767/two-solutions-inner-join-and-two-variables

This is the end of this article about SQL implementation of LeetCode (180. Continuous Numbers). For more relevant SQL implementation of continuous numbers content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!