[LeetCode] 180. Consecutive NumbersWrite a SQL query to find all numbers that appear at least three times consecutively.
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:
|
<<: Design sharing of the download page of the Pengyou.com mobile client (picture and text)
>>: Share JS four fun hacker background effect codes
What is the difference between the green version ...
This article example shares the specific code of ...
Table of contents Preface need accomplish First R...
Use JS to complete a simple calculator for your r...
Inject axios into Vue import axios from 'axio...
Table of contents Preface 1. 404 Page 1. Causes 2...
1. Install MySQL This article is installed via AP...
summary: The following is a method for changing t...
Here are some problems encountered in the use of ...
engine Introduction Innodb engine The Innodb engi...
In the following example, when the width of the td...
Table of contents Basic usage of Promise: 1. Crea...
Table of contents 1.sleep function 2. setTimeout ...
Table of contents 1. What is reflection? 2. Refle...
This article example shares the specific code for...