mysql窗口函数排名查询与连续出现的数字查询

排名查询

学会这一个查询，我们应该对该类型的查询方法就能有一个了解，不然如果下次遇到该类型的查询，我们依然分析不出

给你一张表，里面有id 和score字段，根据score的分数大小排序，假如有相同的分数，按照相同的次序进行排行

例子如下

该decimal 参数代表的是精度，（5，3）代表该数字只能有五个，包括小数点后面的，3代表小数点后面只能有三个意思该字段的范围在 -99.999 到99.999 如果是（5，0）代表着只能是整数，且没有小数点

插入的数据如下

查询到的例子如下

分析

首先我们先查询到 score字段的所有值，然后按照降序排列，然后再看右边的rank字段，我们显然需要对我们的Scores 表进行分表的查询，问题是怎么得到该每个字段的排名，正常的排名的话

会根据 id 等按照顺序查询，但是这样显然得不到我们想要的结果

我们可以分出来一个表命名为s2 然后让s2表中的所有字段跟s1表的所有字段进行统计，如果s2的字段值大于等于s1的字段值就count大于等于s1字段值的个数，根据count出来的个数进行排序就可以了，这样说可能比较抽象我们举个例子

举例

现在我们有两张表 s1 s2 两张表的数据是相同的，对于s1 表中的score 分为5 的进行比对，s2表中发现大于等于s1表中的score 为5 的只有一个 5 ，那么他count出来的数据就是1 ，而对于4.5 ，s2表中大于等于s1表中4.5的数据只有 5 和4.5 那么他count 出来的数据就是 2 ，但是有两个3.5的情况下，大于等于3.5的值有5个，跟排名4不同，那么我们就可以去重处理

sql语句

select s1.score,(
select  count( distinct s2.score) from Scores s2 where s2.score>=s1.score
    )as 'rank'
from Scores s1 order by  s1.score desc;

用窗口函数完成查询

用窗口函数也能达到类似的效果

SELECT
    S.score,
    DENSE_RANK() OVER (
        ORDER BY S.score DESC
        ) AS 'rank'
FROM
    Scores S
ORDER BY
    S.score DESC;

如果是rank（）窗口函数的话排行就会变成

发现会跳过5 这个排行

连续出现的数字

给你一张logs表，查询同一个数字连续出现3次以上的数字，注意是连续出现三次以上

该情景也用到球员得分排名，比如连续得分三次的球员名称等场景

查询结果应该如下

分析

1.三表联查

连续三次出现的情况，我们可以视为一个数字的id 是升序的，而且他连续出现，那么他第一次出现的id =第二次出现的id-1，第二次出现的id 等于第三次出现的id-1，用3表联查就可以，但这也是效率最低的情况

2.lead （）窗口函数

lead（）窗口函数的意思就是

将数据往下平移生成一个新的字段，我们直接看代码与具体实例，

表中的数据还是上文的表数据

select num,
       lag(num,1)over() as a,
       lag(num,2)over () as b  from Logs
    as c

我们查询的出来数据如下

我们把每一个数据往下平移，比如说第二行的原始数据1 .第一行向下平移就是a 列内容为1，因为b列向下平移两次，而num所在对应只有第一行有数据，所以b列为空

然后以此组装生成了一个新的表 c ，我们对表c的数据进行列内容的对比，去重就可以了

select  distinct c.num as ConsecutiveNums
from( select num,
             lag(num,1)over() as a,
             lag(num,2)over () as b  from Logs
    )as c
where c.num=c.a and c.b=c.a;

查询结果