SQL implements LeetCode (180. Continuous numbers)

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!

You may also be interested in:
  • SQL implementation of LeetCode (196. Delete duplicate mailboxes)
  • SQL implementation LeetCode (185. Top three highest salaries in the department)
  • SQL implementation of LeetCode (184. The highest salary in the department)
  • SQL implementation of LeetCode (183. Customers who have never placed an order)
  • SQL implementation of LeetCode (182. Duplicate mailboxes)
  • SQL implementation of LeetCode (181. Employees earn more than managers)
  • C++ implementation of LeetCode (179. Maximum number of combinations)
  • SQL implementation of LeetCode (197. Rising temperature)

<<:  Design sharing of the download page of the Pengyou.com mobile client (picture and text)

>>:  Share JS four fun hacker background effect codes

Recommend

How to use CSS attribute selectors to splice HTML DNA

CSS attribute selectors are amazing. They can hel...

Detailed explanation of Tomcat core components and application architecture

Table of contents What is a web container? The Na...

Set the width of the table to be fixed so that it does not change with the text

After setting the table width in the page to width...

FastDFS and Nginx integration to achieve code analysis

FastDFS & Nginx Integration: The tracker is c...

Explaining immutable values ​​in React

Table of contents What are immutable values? Why ...

Detailed explanation of jQuery's core functions and event handling

Table of contents event Page Loading Event Delega...

How to use async and await correctly in JS loops

Table of contents Overview (Loop Mode - Common) D...

Element dynamic routing breadcrumbs implementation example

To master: localStorage, component encapsulation ...

Use of align-content in flex layout line break space

1. The effect diagram implemented in this article...

Basic knowledge: What does http mean before a website address?

What is HTTP? When we want to browse a website, w...

CocosCreator classic entry project flappybird

Table of contents Development Environment Game en...

Analysis of the Principles of MySQL Slow Query Related Parameters

MySQL slow query, whose full name is slow query l...

Solve the problem of running hello-world after docker installation

Installed Docker V1.13.1 on centos7.3 using yum B...

Detailed explanation of the execution plan explain command example in MySQL

Preface The explain command is the primary way to...

MySQL Basic Tutorial: Detailed Explanation of DML Statements

Table of contents DML statements 1. Insert record...