MySQL - 递归重新排序算法/具有变量增量的更新

Question

很抱歉这种毫无意义的标题，但我想不出一个更合适的标题。

---

我有一个 MySQL 表，它看起来像这样:

SELECT * FROM `table`

+----+-----------+----------+-------+
| id | dimension | order_by | value |
+----+-----------+----------+-------+
|  1 |     1     |     1    |  1st  |
|  2 |     1     |    100   |  3rd  |
|  3 |     2     |    300   |  5th  |
|  4 |     3     |    999   |  6th  |
|  5 |     1     |     2    |  2nd  |
|  6 |     2     |     1    |  4th  |
+----+-----------+----------+-------+

我列出了按 dimension(第一)和 order_by(第二)排序的所有条目，如下所示:

SELECT * FROM `table` ORDER BY `dimension`, `order_by`

+----+-----------+----------+-------+
| id | dimension | order_by | value |
+----+-----------+----------+-------+
|  1 |     1     |     1    |  1st  |
|  5 |     1     |     2    |  2nd  |
|  2 |     1     |    100   |  3rd  |
|  6 |     2     |     1    |  4th  |
|  3 |     2     |    300   |  5th  |
|  4 |     3     |    999   |  6th  |
+----+-----------+----------+-------+

现在我想写一个函数，重新排列 order_by，如果可能的话只用一个 update 查询，让它看起来像那样:

SELECT * FROM `table` ORDER BY `dimension`, `order_by`

+----+-----------+----------+-------+
| id | dimension | order_by | value |
+----+-----------+----------+-------+
|  1 |     1     |     1    |  1st  |
|  5 |     1     |     2    |  2nd  |
|  2 |     1     |     3    |  3rd  |
|  6 |     2     |     1    |  4th  |
|  3 |     2     |     2    |  5th  |
|  4 |     3     |     1    |  6th  |
+----+-----------+----------+-------+

到目前为止我得到了什么(不幸的是，我没有开始对每个维度进行重新计算):

UPDATE `table` AS `l`
JOIN (SELECT @i=1 FROM `table`) AS `i`
SET `order_by` = @i:=i

现在，我的问题是:是否可以只用一个 UPDATE 查询来完成？

Answer 1

您必须引入另一个变量来保存上一行的值。

UPDATE Table1 t
INNER JOIN (
  SELECT
  id, /*your primary key I assume*/
  @new_ob:=if(@prev != dimension, 1, @new_ob + 1) as new_ob,
  @prev := dimension /*In this line, the value of the current row is assigned. In the previous line, the variable still holds the value of the previous row*/
  FROM
  Table1
  , (SELECT @prev := null, @new_ob := 0) var_init_subquery
  ORDER BY dimension, order_by
) st ON t.id = st.id
SET t.order_by = st.new_ob;

• 看到它在 sqlfiddle 中实时运行