BeWithYou

胡搞的技术博客

  1. 首页
  2. 运维/工具
  3. mySQL分组后按指定字段更新排序

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_idreq_count升序排列。子查询rc初始化了两个临时变量@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自己内联一下更新字段即可。

回到顶部