sqlite -- 手动编写 rank() 函数

---

转载请声明，本文来自：https://blog.csdn.net/shijianduan1/article/details/108178332
求一波关注，有用的话，请点个赞。

---

背景

项目一开始定义使用轻量级数据库sqlite来处理数据， 用着用着发现想要做个排序功能
却发现sqlite没有现成的排序函数rank()等等，
使用排序后临时表的rowid也不行， 查询的出来的rowid是真实数据的，并不是临时表；

然后翻阅了百度，也没有找到个现成的。

前言

这个节点换数据库就不现实，函数不也是底层逻辑组合起来的么， 只能自己动手丰衣足食。
本过程没有深究 其他数据库rank()函数的原理，主要目的是实现相同的功能，满足基本使用。

同时也欢迎感兴趣的小伙伴对 SQL代码进行拓展及优化。

实际代码，其实步骤3 就已经结束，后面均是验证。

需求

已知： 成绩分数；
需求： 做一个成绩排行榜
简单分析：
1. 对成绩排序，生成成绩排名后插入数据；
2. 每插入一条成绩，则更新排行榜。

解决过程

表A 和 数据

rowid(数据库自带)	score	rank
1	10	–
2	8	–
3	6	–
4	4	–

步骤1 获取序列

统计比当前成绩高的成绩有多少， 其中a1.*是为了便于查看数据

select count(*) as num,a1.ROWID,a1.*
        from  A a1, A b1
        where a1.score < b1.score
        group by a1.ROWID

结果:

num	rowid	score	rank
1	2	8	–
2	3	6	–
3	4	4	–

步骤2 序列号 转换成 顺序

将表A 与上面查询到的结果进行 左关联，其中c1.*是为了便于查看数据

 with  AA2 AS (
        select count(*) as num,a1.ROWID
        from  A a1, A b1
        where a1.score < b1.score
        group by a1.ROWID)
    select ifnull(AA2.num,0)+1 as rankPlus , c1.*
    from A c1
    left join AA2 on  AA2.ROWID = c1.ROWID

结果：

rankPlus	score	rank
1	10	–
2	8	–
3	6	–
4	4	–

步骤3 更新表的顺序字段

将查询到的结果更新到表A里面

update A set rank = (
     with  AA2 AS (
        select count(*) as num,a1.ROWID
        from  A a1, A b1
        where a1.score < b1.score
        group by a1.ROWID)
    select ifnull(AA2.num,0)+1 as rankPlus /*, c1.**/
    from A c1
    left join AA2 on  AA2.ROWID = c1.ROWID
    where c1.ROWID = A.ROWID
) where score < 11

这里需要说明下 where score <11 是因为 update语句一定要有条件跟随，不然会整个表的数据都更新。

步骤4 结果校验

执行select * from A 查看下结果校验下。

结果：

rowid(数据库自带)	score	rank
1	10	1
2	8	2
3	6	3
4	4	4

步骤5 初步优化-更新部分数据

上述表 是结果优化，其实每插入一条数据，
有顺序变更的是成绩不如插入成绩的那些数据

insert into A values( 7,null);
update A set rank = (
     with  AA2 AS (
        select count(*) as num,a1.ROWID
        from  A a1, A b1
        where a1.score < b1.score
        group by a1.ROWID)
    select ifnull(AA2.num,0)+1 as rankPlus /*, c1.**/
    from A c1
    left join AA2 on  AA2.ROWID = c1.ROWID
    where c1.ROWID = A.ROWID
) where score <= 7 ; --和插入数据的 成绩 7 保持一致
select * from A ;

结果：

rowid(数据库自带)	score	rank
1	10	1
2	8	2
3	6	4
4	4	5
5	7	3

步骤6 测试-插入相同成绩

将步骤5 的脚本再次执行一遍，即再插入一次 成绩7 。
结果：

rowid(数据库自带)	score	rank
1	10	1
2	8	2
3	6	5
4	4	6
5	7	3
6	7	3

最终代码

我这里将 rowid当作数据的唯一主键来使用了，
若rowid会因临时表变动而变动，则更简单了，都可以省略步骤2 中的左连接内容了

insert into A values( 7,null);
update A set rank = (
     with  AA2 AS (
        select count(*) as num,a1.ROWID
        from  A a1, A b1
        where a1.score < b1.score
        group by a1.ROWID)
    select ifnull(AA2.num,0)+1 as rankPlus /*, c1.**/
    from A c1
    left join AA2 on  AA2.ROWID = c1.ROWID
    where c1.ROWID = A.ROWID
) where score <= 7 ; --和插入数据的 成绩 7 保持一致

文章到此结束，欢迎交流。