顺序更新mysql表

1l5u6lss 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(313)

给定下表，如何按顺序重新排序 position 从1到n在删除一行或多行后使用单个查询，并且仍然保留 position ?

+---------+----------+-----+
| id (pk) | position | fk  |
+---------+----------+-----+
|       4 |        1 | 123 |
|       2 |        2 | 123 |
|      18 |        3 | 123 |
|       5 |        4 | 123 |
|       3 |        5 | 123 |
+---------+----------+-----+

例如，如果 position =1 ( id =4），则所需的最终记录为：

+---------+----------+-----+
| id (pk) | position | fk  |
+---------+----------+-----+
|       2 |        1 | 123 |
|      18 |        2 | 123 |
|       5 |        3 | 123 |
|       3 |        4 | 123 |
+---------+----------+-----+

如果 position =3 ( id =18），则所需的最终记录为：

+---------+----------+-----+
| id (pk) | position | fk  |
+---------+----------+-----+
|       4 |        1 | 123 |
|       2 |        2 | 123 |
|       5 |        3 | 123 |
|       3 |        4 | 123 |
+---------+----------+-----+

如果只删除了一行，而不删除多行，我可以执行如下操作。

DELETE FROM mytable WHERE fk=123 AND position = 4;
UPDATE mytable SET position=position-1 WHERE fk=123 AND position > 4;

sql mysql sequential

来源：https://stackoverflow.com/questions/50817252/sequentially-update-mysql-table

2条答案

按热度按时间

qhhrdooz1#

可以使用update和row\ u number（）函数。如果你按位置订购，应该没问题。

UPDATE [1]
SET POSITION = [2].RN
FROM t [1]
JOIN (
       SELECT 
           t.ID
           , ROW_NUMBER() OVER (ORDER BY POSITION DESC) AS RN
       FROM t
     ) [2] 
ON [1].id = [2].id

正如人们提到的，这不适用于mysql。很抱歉，我没有看到标签，所以没有收到信息。

赞(0）回复(0）举报 2021-06-21

roqulrg32#

如果您还没有使用mysql 8（它提供了诸如row\u number（）之类的窗口函数），请使用用户定义的变量：

UPDATE t
JOIN (

    SELECT 
    t.*
    , @n := @n + 1 as n
    FROM t
    , (SELECT @n := 0) var_init
    ORDER BY position

) sq ON t.id = sq.id
SET t.position = sq.n;

看它在小提琴里现场演奏
奖金：
当你有多个小组时，情况会稍微复杂一些。
例如，对于这样的示例数据

|  id | position |  fk |
|-----|----------|-----|
|   4 |        1 | 123 |
|   2 |        2 | 123 |
|   5 |        4 | 123 |
|   3 |        5 | 123 |
|  40 |        1 | 234 |
|  20 |        2 | 234 |
| 180 |        3 | 234 |
|  30 |        5 | 234 |

查询将是

UPDATE t
JOIN (

    SELECT 
    t.*
    , @n := if(@prev_fk != fk, 1, @n + 1) as n
    , @prev_fk := fk
    FROM t
    , (SELECT @n := 0, @prev_fk := NULL) var_init
    ORDER BY fk, position

) sq ON t.id = sq.id
SET t.position = sq.n;

在这里，您只需将当前fk保存在另一个变量中。当处理下一行时，变量仍保留“前一行”的值。然后重新设置 @n 变量，当值更改时。
看它在小提琴里现场演奏
更新：
在mysql 8中，可以使用window函数 row_number() 这样地：

update t join (
    select t.*, row_number() over (partition by fk order by position) as new_pos 
    from t
) sq using (id) set t.position = sq.new_pos;

赞(0）回复(0）举报 2021-06-21

我来回答

顺序更新mysql表

2条答案

相关问题

热门标签

最新问答