SQL练习笔记四：连续出现的数字

用户6184845

发布于 2021-01-04 12:44:49

8670

发布于 2021-01-04 12:44:49

问题：编写一个 SQL 查询，查找所有至少连续出现三次的数字。

CREATE TABLE Log(
  Id int(4) not null,
  num int(4) not null
);

INSERT INTO Log VALUES(1,1);
INSERT INTO Log VALUES(2,1);
INSERT INTO Log VALUES(3,1);
INSERT INTO Log VALUES(4,2);
INSERT INTO Log VALUES(5,1);
INSERT INTO Log VALUES(6,2);
INSERT INTO Log VALUES(7,2);
INSERT INTO Log VALUES(8,2);

-- 创建3个Log表副本L1,L2,L3 通过Id连续相比且num相等实现 --
SELECT DISTINCT
  L1.num AS ConsecutiveNums 
FROM
  Log AS L1,
  Log AS L2,
  Log AS L3 
WHERE
  L1.Id = L2.Id - 1 
  AND L2.Id = L3.Id - 1 
  AND L1.num = L2.num 
  AND L2.num = L3.num;