给单表查询结果添加一列表示名次(order_no)和行号(row_no)
需求信息
有一个游戏子任务表(qw_game_task),查出该表的所有记录,并在查询结果里添加两列分别表示名次(order_no)和行号(row_no)。名次(order_no)和行号(row_no)的区别:
名次(order_no):编号从1开始,用一section_id的order_no值相同表示名次并列,递增排序;
行号(row_no):递增排序,从1开始;
数据库表结构
qw_game_task
游戏子任务
字段名
中文名
字段类型
备注说明
关联表
可选项
id
表主键
integer
自动增长
section_id
主任务ID
integer
qw_game_section
default: 0
name
子任务名
string
default: ""
description
任务介绍信息
text
task_num
子任务的排序编号
integer
同一section_id task_num小的排在前面
default: 0
status
子任务状态
boolean
default: false
editor_id
编辑用户ID
integer
default: 0
created_time
创建时间
integer
updated_time
更新时间
integer
查询语句
set @current_section_id = 0, @order_no:=1, @row_no = 0;
SELECT id, section_id, name, @current_section_id AS before_section_id, @row_no:=@row_no+1 AS row_no,
CASE WHEN @current_section_id <> section_id THEN @order_no:=@row_no ELSE @order_no:=@order_no END AS order_no
, @current_section_id := section_id AS after_section_id
FROM qw_game_task
ORDER BY id DESC
查询结果截图:
实现分析
行号(row_no):实现比较简单,在整个SELECT语句执行前定义一个变量@row_no并赋值为0,然后在SELECT语句里,每次自动加1就可以;
名次(order_no):比row_no稍微复杂一点,需要定义两个变量@current_section_id和@order_no。@current_section_id记录前一条记录的section_id,然后通过对比当前记录的section_id和@current_section_id是否相等来给@order_no赋值。因为我们在判断@current_section_id和sectio_id是否相等后,把当前记录的section_id赋值给@current_section_id,所以查询结果里的before_section_id和after_section_id会不同。
补充说明
1,网上有人说下面这种写法和上面的SQL语句的查询结果相同,但本人测试结果有不一样。
SELECT id, section_id, name, @current_section_id AS before_section_id, @row_no:=@row_no+1 AS row_no,
CASE WHEN @current_section_id <> section_id THEN @order_no:=@row_no ELSE @order_no:=@order_no END AS order_no
, @current_section_id := section_id AS after_section_id
FROM qw_game_task, (SELECT @current_section_id = 0, @order_no:=1, @row_no = 0) r
ORDER BY qw_game_task.id DESC;
2,如果我们只想取出查询结果里ID<=50(order_no>1,row_no >=13)的记录,无论是使用WHERE或LIMIT子语句,row_no都是从1重新开始编号(原因请见:SQL语句的执行原理分析)。
1)WHERE子语句,查询结果
2)LIMIT子语句查询结果
素材文件:链接: https://pan.baidu.com/s/1bpEJ82F 密码: ktfs