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;

查询结果 

还有一个窗口函数row_number()窗口函数也是类似于该窗口函数的原理一样，生成一个新的表，对比一下，得出结论