MYSQL 和 MYBATIS 对查询出的结果集 增加 自增 序号列/排序列
目录
参考五:(允许出现同名次:出现并列的名次,比如出现两个第二名,接着就是第四名)
今天做了一个关于排行榜的功能
大概就是对粉丝总数的值进行倒序排序,取粉丝数最多的前一百名,需要返回对应的名次;
这里特殊说明一下,如果粉丝数相同,则按达到条件时间的先后排序,不允许出现名次相同的现象;
我这就直接上代码了啊,逻辑什么的大家自己看看就行,具体的解释 在下面的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可以正常的查询 出来数据.
参考五:(允许出现同名次:出现并列的名次,比如出现两个第二名,接着就是第四名)
-
按分数统计排名查询怎么写,比如user表如下:
-
id 分数(grade)
-
1 90
-
2 87
-
3 87
-
4 80
-
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总变量不用事先声明,在用的时候直接用@变量名 使用即可