根据排序列和另一个列值对表重新排序 [英] re-order a table based on sort column and another column value

问题描述

我有一个表，该表有一个sort_no列，排序值属于q_id，它对应于问题ID.但是，它不包括适当的排序值.有时，对于具有相同q_id的不同记录，将重复排序编号.我必须使用每个问题的唯一排序号来重构该表.

I have a table which has a sort_no column and the sort values are belonging to q_id which corresponds to question id. But, it does not include proper sorting values. Sometimes, the sort numbers are being repeated for different records with the same q_id. I have to refactor this table with unique sort numbers for each question.

这是我已经拥有的示例数据:

This is a sample data I already have:

 id |   name  | sort_no | q_id
-------------------------------
 1  |  val_1  |    1    | 21
 2  |  val_2  |    2    | 21
 3  |  val_3  |    1    | 32
 4  |  val_4  |    3    | 21
 5  |  val_5  |    2    | 32
 6  |  val_6  |    2    | 32
 7  |  val_7  |    1    | 25
 8  |  val_8  |    1    | 21
 9  |  val_9  |    1    | 21
-------------------------------

这应该是这样:

 id |   name  | sort_no | q_id
------------------------------
 1  |  val_1  |    1    | 21
 2  |  val_2  |    2    | 21
 3  |  val_3  |    1    | 32
 4  |  val_4  |    3    | 21
 5  |  val_5  |    2    | 32
 6  |  val_6  |    3    | 32
 7  |  val_7  |    1    | 25
 8  |  val_8  |    4    | 21
 9  |  val_9  |    5    | 21
-------------------------------

实际上，我可以获取记录并将其放入循环中并通过循环进行更新.但是，如您所知，这需要时间和资源.该表是巨大的，具有数百万条记录.

Actually, I can fetch the records and put them in a loop and update it by a loop. But, as you know, it takes time and resource. The table is huge with millions of records.

我想知道是否可以直接在MySQL中使用嵌套查询.

I was wondering if I could do it directly in MySQL with a nested query.

我对查询一无所知.

以前有人经历过吗?

推荐答案

update test5
   set sort_no=@srt:=if(@grp=q_id,@srt+1,1),
       q_id=@grp:=q_id
 where (0,0)=(select @grp:=0,@srt:=0)
 order by q_id, `name`

设置所需的排序依据". 排序依据"中的第一列必须为"q_id".

Set needed 'order by'. First column in 'order by' must be "q_id".

注意::在运行此查询之前，应禁用更新安全模式(如果不是默认情况):

NOTE: before running this query, the update safe mode should be disabled (if not by default):

SET SQL_SAFE_UPDATES = 0;

这篇关于根据排序列和另一个列值对表重新排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！