LeetCode-180.连续出现的数字

最新推荐文章于 2024-02-26 17:08:36 发布

DotNeter-Hpf

最新推荐文章于 2024-02-26 17:08:36 发布

阅读量190

点赞数 1

分类专栏：数据库文章标签： mysql leetcode

本文链接：https://blog.csdn.net/Myzhouzhou/article/details/107850802

版权

数据库专栏收录该内容

16 篇文章 0 订阅

订阅专栏

题目：

编写一个 SQL 查询，查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按任意顺序排列。

表: Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
|     id      |   int   |
|     num     | varchar |
+-------------+---------+
id是这个表的主键

返回如下结果

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

添加表结构和数据

DROP TABLE IF EXISTS `logs`;
CREATE TABLE `logs`  (
  `Id` int(11) NOT NULL,
  `Num` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `logs` VALUES (1, 1);
INSERT INTO `logs` VALUES (2, 1);
INSERT INTO `logs` VALUES (3, 1);
INSERT INTO `logs` VALUES (4, 2);
INSERT INTO `logs` VALUES (5, 1);
INSERT INTO `logs` VALUES (6, 2);
INSERT INTO `logs` VALUES (7, 2);

思路：

# Id是连续的，数字是相等的

解答：

SELECT DISTINCT
    L1.Num AS ConsecutiveNums 
FROM
    LOGS AS L1,
    LOGS AS L2,
    LOGS AS L3 
WHERE
    L1.Id = L2.Id - 1 
    AND L2.Id = L3.Id - 1 
    AND L1.Num = L2.Num 
    AND L2.Num = L3.Num