SQL实现LeetCode(180.连续的数字)

[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.

这道题给了我们一个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;

参考资料：

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

到此这篇关于SQL实现LeetCode(180.连续的数字)的文章就介绍到这了,更多相关SQL实现连续的数字内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • SQL实现LeetCode(177.第N高薪水)

    [LeetCode] 177.Nth Highest Salary 第N高薪水 Write a SQL query to get the nth highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1  | 100    | | 2  | 200    | | 3  | 300    | +----+--------+ For example, given the ab

  • SQL实现LeetCode(175.联合两表)

    [LeetCode] 175.Combine Two Tables 联合两表 Table: Person +-------------+---------+ | Column Name | Type    | +-------------+---------+ | PersonId    | int     | | FirstName   | varchar | | LastName    | varchar | +-------------+---------+ PersonId is the

  • SQL实现LeetCode(178.分数排行)

    [LeetCode] 178.Rank Scores 分数排行 Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, th

  • SQL实现LeetCode(176.第二高薪水)

    [LeetCode] 176.Second Highest Salary 第二高薪水 Write a SQL query to get the second highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1  | 100    | | 2  | 200    | | 3  | 300    | +----+--------+ For example, given

  • SQL实现LeetCode(180.连续的数字)

    [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  |

  • Android实现多个连续带数字圆圈效果

    Android实现多个连续带数字圆圈效果

    有项目需求需要绘制多个圆圈,并且使用连续的数字对其排列起来,也就是好多排的圆圈. 首先看一下效果图: 一排设置为8个,一共有53个的: 一排设值为5个的,一共有153个: 可以根据总的个数和每排个数自动调节圆圈的大小,并且根据传入的监听事件作出不同的点击效果. 思路很简单,首先需要画一个圆出来: <?xml version="1.0" encoding="UTF-8"?> <shape xmlns:android="http://sche

  • Mybatis 动态sql if 判读条件等于一个数字的案例

    在Mybatis中 mapper中 boolean updateRegisterCompanyFlag(@Param(value = "companyId") String companyId, @Param(value = "flag") String flag); 传入的flag类型为String,但在mapper.XML中进行判断是下意识地以为判断的值要加上引号 <if test=" '4' == flag "> , LAST_

  • C++实现LeetCode(137.单独的数字之二)

    [LeetCode] 137. Single Number II 单独的数字之二 Given a non-empty array of integers, every element appears three times except for one, which appears exactly once. Find that single one. Note: Your algorithm should have a linear runtime complexity. Could you

  • C++实现LeetCode(136.单独的数字)

    [LeetCode] 136.Single Number 单独的数字 Given a non-empty array of integers, every element appears twice except for one. Find that single one. Note: Your algorithm should have a linear runtime complexity. Could you implement it without using extra memory?

  • SQL实现LeetCode(185.系里前三高薪水)

    [LeetCode] 185.Department Top Three Salaries 系里前三高薪水 The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id. +----+-------+--------+--------------+ | Id | Name  | Salary | DepartmentId | +--

随机推荐