代码之家 › 专栏 › 技术社区 › user1032531

顺序更新mysql表

sequential sql mysql

user1032531 · 技术社区 · 8 年前

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

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

例如,如果 位置 = 1 id =4)已删除,所需的最终记录为:

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

如果 位置 = 3 身份证件 =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;

2 回复 | 直到 8 年前

fancyPants 8 年前

User-defined variables 如果您还没有使用mysql 8(它提供了诸如row_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;

看到它在现场工作 sqlfiddle

奖金:

当你有多个小组的时候,事情会变得稍微复杂一些。
例如,对于这样的示例数据

|  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 变量,当值改变时。

看到它在现场工作 sqlfiddle

更新:

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

dimo raichev 8 年前

您可以使用update和 ROW_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。很抱歉,我没有看到标签。