Sql练习--查询连续出现三次的数据

 

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

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

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+----+
|Num |
+----+
| 1  |
+----+

连续出现的意味着相同数字的 Id 是连着的，我们 并检查是否有 3 个连续的相同数字。

创建表语句（环境Sqlserver数据库）

Create Table logs(id int,Num int)
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)

查询3个连续相同的数字

语法1：Sqlserver、Mysql、Oracle通用

表A的id + 1等于表B的id 并且表A的Num 等于表B的Num(只能够说明两个连续的Id有相同的Num值)所以我们需要在加一个条件即

表B的id + 1等于表C的id 并且表B的Num 等于表C的Num(即A、B、C三个表的ID连续并且Num值还相等满足条件)如下Sql所示

SELECT A.Num FROM logs AS a
INNER JOIN logs AS B ON A.id+1 = B.id and A.Num = B.Num
INNER JOIN logs AS C ON B.id+1 = C.id and B.Num = C.Num

但是这只是满足测试集的需求，当我们在表中继续插入一条数据

INSERT INTO logs VALUES(0,1)

在进行查询

SELECT A.Num FROM logs AS a
INNER JOIN logs AS B ON A.id+1 = B.id and A.Num = B.Num
INNER JOIN logs AS C ON B.id+1 = C.id and B.Num = C.Num

得到下图结果，会产生两个1，然而这并不是想要的结果，所以我们在此Sql上需加上distinct去重操作，或者Group by 操作

SELECT DISTINCT A.Num FROM logs AS a
INNER JOIN logs AS B ON A.id+1 = B.id and A.Num = B.Num
INNER JOIN logs AS C ON B.id+1 = C.id and B.Num = C.Num
--或者
SELECT  A.Num FROM logs AS a
INNER JOIN logs AS B ON A.id+1 = B.id and A.Num = B.Num
INNER JOIN logs AS C ON B.id+1 = C.id and B.Num = C.Num
GROUP BY A.NUM

但是此Sql执行速度略慢

对Sql进行优化（Sqlserver、Oracle通用）

SELECT DISTINCT(Num) AS Num 
FROM (SELECT Num,id,lag(id,2) OVER(partition by num order by id) AS id_new FROM logs) A 
WHERE A.id = A.id_new + 2

查询速度略有加快

Oracle从8.1.6、sqlserver 开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

Parttion by 关键字是Oracle中分析性函数的一部分，它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，儿聚合函数一般只有一条反映统计值的结果。

lead(列名,n,m):  当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null。

lag(列名,n,m):  当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null。

也就是说利用lag函数向前查询这个id_new前2行记录的值是否于源id列的值相等，如果相等即有三个连续的值，这里我没有指定第三列的M的值默认为null。

Mysql的没有lag() over函数 可以利用其他方法解，明日追加Mysql的另一种解法