转载请声明,本文来自:https://blog.csdn.net/shijianduan1/article/details/108178332
求一波关注,有用的话,请点个赞。
项目一开始定义使用轻量级数据库sqlite来处理数据, 用着用着发现想要做个排序功能
却发现sqlite没有现成的排序函数rank()
等等,
使用排序后临时表的rowid
也不行, 查询的出来的rowid
是真实数据的,并不是临时表;
然后翻阅了百度,也没有找到个现成的。
这个节点换数据库就不现实,函数不也是底层逻辑组合起来的么, 只能自己动手丰衣足食。
本过程没有深究 其他数据库rank()
函数的原理,主要目的是实现相同的功能,满足基本使用。
同时也欢迎感兴趣的小伙伴对 SQL代码进行拓展及优化。
实际代码,其实步骤3 就已经结束,后面均是验证。
已知: 成绩分数;
需求: 做一个成绩排行榜
简单分析:
1. 对成绩排序,生成成绩排名后插入数据;
2. 每插入一条成绩,则更新排行榜。
rowid(数据库自带) | score | rank |
---|---|---|
1 | 10 | – |
2 | 8 | – |
3 | 6 | – |
4 | 4 | – |
统计比当前成绩高的成绩有多少, 其中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 | – |
将表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 | – |
将查询到的结果更新到表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
语句一定要有条件跟随,不然会整个表的数据都更新。
执行select * from A
查看下结果校验下。
结果:
rowid(数据库自带) | score | rank |
---|---|---|
1 | 10 | 1 |
2 | 8 | 2 |
3 | 6 | 3 |
4 | 4 | 4 |
上述表 是结果优化,其实每插入一条数据,
有顺序变更的是成绩不如插入成绩的那些数据
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 |
将步骤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 保持一致
文章到此结束,欢迎交流。