MYSQL 和 MYBATIS 对查询出的结果集 增加 自增 序号列/排序列

目录

今天做了一个关于排行榜的功能

参考一：

第一种方法：

第二种方法：

参考二：

对于  变量  i  的分析： 

参考三（很全面易懂）：

一、变量的定义及使用

二、变量的赋值

三、变量使用小技巧

参考四：

问题一

问题二

参考五：（允许出现同名次：出现并列的名次，比如出现两个第二名，接着就是第四名）

参考六（最全最详细的）：

---

今天做了一个关于排行榜的功能

大概就是对粉丝总数的值进行倒序排序，取粉丝数最多的前一百名，需要返回对应的名次；

这里特殊说明一下，如果粉丝数相同，则按达到条件时间的先后排序，不允许出现名次相同的现象；

我这就直接上代码了啊，逻辑什么的大家自己看看就行，具体的解释 在下面的6个参考中：

<select id="rankFansList" resultType="java.util.HashMap">
select
@rownum:=@rownum+1 as rowNum ,
amount,
userId,
userName,
avatarUrl,
rankFlag
from
(select @rownum := 0) as rownum,
(select
count(uor.user_id) as amount,
uor.source_id as userId,
case uor.source_id when #{userId} then 1 else 0 end as rankFlag,
<if test="userType != null and userType == 1">
up.nick_name as userName,
up.avatar_url as avatarUrl
</if>
<if test="userType != null and userType == 2">
ut.nick_name as userName,
ut.avatar_url as avatarUrl
</if>
<if test="userType != null and userType == 3">
ui.nick_name as userName,
ui.avatar_url as avatarUrl
</if>
from user_operate_record uor
<if test="userType != null and userType == 1">
left join user_parent up on uor.user_id = up.user_id
</if>
<if test="userType != null and userType == 1">
left join user_teacher ut on uor.user_id = ut.user_id
</if>
<if test="userType != null and userType == 1">
left join user_institution ui on uor.user_id = ui.user_id
</if>
where uor.handle_type = 4 and uor.praise_status = 1 and uor.source_user_type = #{userType}
and (
uor.create_time between unix_timestamp(date_format(NOW(), '%Y-%m-01')) and unix_timestamp(date_format(last_day(curdate()), '%y-%m-%d 23:59:59'))
or
uor.update_time between unix_timestamp(date_format(NOW(), '%Y-%m-01')) and unix_timestamp(date_format(last_day(curdate()), '%y-%m-%d 23:59:59'))
)
group by uor.source_id
order by count(uor.user_id) desc,uor.create_time desc
) k
where k.amount > 0
</select>

---

以下是我在网上找到的一些相关资料：

参考一：

https://blog.csdn.net/qq_34365173/article/details/80869475

第一种方法：

select   (@i:=@i+1)   as   i,user_manage.*   from   user_manage,(select   @i:=0)   as   it ；

@i:=@i+1 as i 会生成一个排序的i，在查询的时候，会在结果集中显示，@i:0 的意思是i从1开始排序。

第二种方法：

set @rownum=0;

 select @rownum:=@rownum+1 as rownum, t.name from user_manage t;

---

参考二：

https://blog.csdn.net/arbben/article/details/78665389

对于  变量  I  的分析： 

在开始是定义一个变量i，让它每增一条结果是 +1，@i:=1； 
这里顺带复习下mysql定义用户变量的方式：select @变量名 
对用户变量赋值有两种方式，一种是直接用”=”号，另一种是用”:=”号。其区别在于使用set命令对用户变量进行赋值时，两种方式都可以使用；当使用select语句对用户变量进行赋值时，只能使用”:=”方式，因为在select语句中，”=”号被看作是比较操作符 
(@i:=@i+1) 也可以写成 @i:=@i+1，加括号是为了视觉上看这结构更清楚些。在定义好一个变量后每次查询都会给这个变量自增，而我们每次执行查询语句获取结果后就不需要这个变量自增了，所以要把它重置为0，在表名后用逗号分格下使用 (SELECT @i:=0) as i 就可以了，说下这个as i为什么要这样用，是因为派生表必须需要一个别名，这个就是做它的别名，可以任意字符。 

---

参考三（很全面易懂）：

https://blog.csdn.net/qq_38377190/article/details/98488030

SQL语句定义变量，如下：

-- 定义变量并赋值
SET @c = '2333';

-- 查询定义的变量的值
SELECT @c;

执行结果

2333

那么如何在mybatis的框架实现变量的定义的呢？如果能实现变量的定义，那就可以在mybatis上写一定量的业务代码，也是不错的！

一、变量的定义及使用

<update id="updatePrice">
/*1.变量定义*/
select @isEnd := 0, @isFinish := 1;

/*2.使用变量*/
update Sc_Stock a set a.cost_price = 10 where @isFinish= 1 and @isEnd = 0;
</update>

二、变量的赋值

<update id="updatePrice">
/*1.变量定义*/
select @newPrice:=0;
/*2.变量赋值*/
update Sc_Stock a set a.price = @newPrice := 11 where a.id = 2222;
/*3.变量使用*/
update Sc_Stock a set a.num = 2 where @newPrice = 11;
</update>

三、变量使用小技巧

<update id="updatePrice">
/*1.变量定义*/
select @isFinish:=0;
/*2.变量使用*/
update Sc_Stock set num = 2 where newPrice = 11 and if(@isFinish = 0, 0, @isFinish := a.is_finish);
</update>

解释一下：上方更新语句最后会变成

update Sc_Stock  set num = 2 where newPrice = 11 and 0;

或是

update Sc_Stock  set num = 2 where newPrice = 11 and 1;

---

参考四：

mybatis中使用sql变量：https://blog.csdn.net/qq_28736639/article/details/101553522

需求背景
在一个这样的业务中，需要查询一个表A的记录，其中A表的主要字段有 id , createtime ,amount ，现在要求给定一个数字N，查询按照创建时间顺序排列之后的前 x条记录，这x条记录需要满足sum(amount)>=N并且尽可能的小的条件。其中传入的参数只有N。在这种情况下，笔者第一个想到的方案就是在sql中使用变量来记录这个sum(amount)的值来达到目的。其中sql如下:

-- 这个sql语句少了一个括号，原作者写的缺了一个，诸君看的时候，自己补全一下
SET @sum =0;
SET @flag =0;
SELECT
*
FROM (
SELECT
(CASE WHEN @sum < 100 THEN @sum := @sum + restpoints ELSE @sum := @sum +
restpoints END) AS sum,
(CASE WHEN @sum <100 THEN @flag :=0 ELSE @flag := @flag + 1 END) AS flag,
(CASE WHEN @flag < 2 THEN id ELSE NULL END) AS xxid,
(CASE WHEN @flag < 2 THEN restpoints ELSE NULL END) AS xxrestpoints,
t.*
FROM (select * from A) a
order by date(useexpiretime) asc,restpoints desc    
)t
)AS TMP
WHERE TMP.xxid IS NOT NULL

问题一

遇到的问题就是mybatis似乎不支持上面的这种写法，会报语法错误；
在确定这个sql在xml中没有书写错误的情况下，开始进行sql的一些变形，来尝试看是否可以。
第一个尝试就是把SET @sum =0;SET @flag =0; 去掉了，放在了 select 里面去定义，因为我发现，即使是只有SET语句也是会报错的，推测mybatis可能不支持SET。

即变化如下：
SELECT
@sum:= 0,
@flag := 0,
TMP.*
FROM (
SELECT
(CASE WHEN @sum < 100 THEN @sum := @sum + restpoints ELSE @sum := @sum +
restpoints END) AS sum,
(CASE WHEN @sum <100 THEN @flag :=0 ELSE @flag := @flag + 1 END) AS flag,
(CASE WHEN @flag < 2 THEN id ELSE NULL END) AS xxid,
(CASE WHEN @flag < 2 THEN restpoints ELSE NULL END) AS xxrestpoints,
t.*
FROM (select * from A) a
order by date(useexpiretime) asc,restpoints desc
)t
)AS TMP
WHERE TMP.xxid IS NOT NULL

这个时候发现，mybatis查询得时候不会报错了，很好，到这个时候基本问题就解决了。

问题二

但是紧接着就发现查询出来的数据都是空的，我使用日志打印出来的sql进行查询发现也是空的，
但是使用之前未变形的那种是正常的，这时候可能就是flag 和sum的值 有问题了，
我是用select @flag进行查看，发现它的值果然不是0，而是19，这让我感到很奇怪，
就是说外层的赋值似乎不等同于SET这种赋值方式，
仔细想想，嵌套查询的情况都是从子查询开始执行的，所以可能在里面的子查询开始的时候，外层的赋值并没有起到作用，
所以，我把这个赋值作为一个单独的子查询放在了最里面，

如下：
SELECT
@sum:= 0,
@flag := 0,
TMP.*
FROM (
SELECT
(CASE WHEN @sum < 100 THEN @sum := @sum + restpoints ELSE @sum := @sum +
restpoints END) AS sum,
(CASE WHEN @sum <100 THEN @flag :=0 ELSE @flag := @flag + 1 END) AS flag,
(CASE WHEN @flag < 2 THEN id ELSE NULL END) AS xxid,
(CASE WHEN @flag < 2 THEN restpoints ELSE NULL END) AS xxrestpoints,
t.*
FROM (select * from A) a
order by date(useexpiretime) asc,restpoints desc
)t,
(SELECT @sum:= 0,@flag := 0) notuse
AS TMP
WHERE TMP.xxid IS NOT NULL

最终，这个sql可以正常的查询 出来数据.

---

参考五：（允许出现同名次：出现并列的名次，比如出现两个第二名，接着就是第四名）

1.  
   按分数统计排名查询怎么写，比如user表如下：
2.  
   id 分数（grade）
3.  
   1 90
4.  
   2 87
5.  
   3 87
6.  
   4 80
7.  
   5 79

需求：出现并列的名次，比如出现两个第二名，接着就是第四名

select
id,
grade,
ifnull( (select count(*) from testSort where grade > t.grade),0 ) +1 as rankValue
from testSort t

结果：

---

参考六（最全最详细的）：

https://www.cnblogs.com/northern-light/p/8494711.html

 

 

----------------------------------------------另一篇文章

在hive和oracle等数据库中，我们有开窗函数row_number可以快速便捷的生成序号。但是在mysql中，似乎不那么容易实现。其实在MySQL中一样可以实现。

话不多说，先上代码！！！

SELECT
a.*, @rank :=@rank+1 as no
FROM( ]
SELECT *
from `student_score_table`
ORDER BY `score` desc
) a
LEFT JOIN (
SELECT @rank :=0
) b
on 1 = 1

首先，需要先定义一个变量rank，然后与排序好的数据表做关联，依次做自增就好。

这里简单介绍一下MySQL中变量的定义：
使用set或select直接赋值,变量名以 @开头

赋值符号：使用set 时可以用 “=“或者“：=” 但是使用select赋值时必须使用“：=”赋值

mysql总变量不用事先声明，在用的时候直接用@变量名 使用即可