MySQL给查询结果添加一表表示行号或名次（1）

最新推荐文章于 2022-09-03 10:42:32 发布

weixin_33923762

最新推荐文章于 2022-09-03 10:42:32 发布

阅读量436

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/laifuzi/blog/861222

版权

2019独角兽企业重金招聘Python工程师标准>>>

给单表查询结果添加一列表示名次（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;