mySQL分组后按指定字段更新排序
遇到一个场景,需要更新某张表my_table
,按照context_id
分组后,再按照re_count
字段增序来更新其rank
字段。
表格内容如下:
id | context_id | req_count | rank |
---|---|---|---|
1 | 1 | 10 | NULL |
2 | 2 | 9 | NULL |
3 | 1 | 8 | NULL |
4 | 2 | 7 | NULL |
5 | 2 | 6 | NULL |
期望更新后结果如下,其中rank从0开始:
id | context_id | req_count | rank |
---|---|---|---|
1 | 1 | 10 | 1 |
2 | 2 | 9 | 2 |
3 | 1 | 8 | 0 |
4 | 2 | 7 | 1 |
5 | 2 | 6 | 0 |
解决方法需要用到临时变量。
UPDATE merchant_request m
INNER JOIN
(
SELECT id, context_id, req_count,
@rank_t:=CASE
WHEN @context_id_t <> context_id THEN 0
ELSE @rank_t+1
END AS rank,
@context_id_t:=context_id AS context_id_tmp
FROM
(SELECT @rank_t:=-1) r,
(SELECT @context_id_t:=-1) c,
(SELECT context_id, id, req_count FROM merchant_request ORDER BY context_id, id) t
) AS rank_table ON m.id = rank_table.id
SET m.rank = rank_table.rank;
子查询t
中将内容按照context_id
和req_count
升序排列。子查询r
和c
初始化了两个临时变量@rank_t
和@context_id_t
值为-1。
子查询rank_table
可以理解为遍历t
的结果,当@context_id_t
跨了group时,重新初始化@rank_t
为0,并当做rank
输出,否则@rank_t
自增。每次遍历结束设置@context_id_t
为当前context_id
。
最后既然得到了排序的结果,再按id自己内联一下更新字段即可。