duanbigan7765
2016-08-24 19:28
浏览 79
已采纳

根据排序列和另一个列值重新排序表

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
-------------------------------

This is what it should be:

 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.

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

I have no idea about the query.

Have anybody experienced this before?

图片转代码服务由CSDN问答提供 功能建议

我有一个表有一个 sort_no 列,排序值属于< 代码> q_id ,对应于问题ID。 但是,它不包括正确的排序值。 有时,使用相同的 q_id 为不同的记录重复排序编号。 我必须使用每个问题的唯一排序编号重构此表。

这是我已有的示例数据:

  id | 名字|  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 | 名字|  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 
 ------------------------------- 
   
 
 

实际上,我可以获取记录并将它们放在循环中并通过循环更新它。 但是,如你所知,这需要时间和资源。 该表非常庞大,有数百万条记录。

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

我没有 关于查询的想法。

有没有人经历过这个?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongxu0690 2016-08-24 20:16
    已采纳
    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`
    

    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;
    
    点赞 评论
  • dongshi1207 2016-08-24 19:39

    ORDER BY doesn't have to be just a field or a list of fields. it can be arbitrary expressions too, e.g.

    ORDER BY foo = 42 DESC, foo
    

    would put all records with 42 FIRST, then the rest of them in regular numeric order.

    If you can come up with some expression that calculates a value that fits your desired sort order, then...

    ORDER BY somefunc(...)
    

    would also work. What matters is the value returned by the function, which is what the DB will use to do the actual sorting.

    点赞 评论

相关推荐 更多相似问题