可以僅使用一個 UPDATE 語句顯式保存 ORDER BY 語句的排序嗎？

我有這張桌子：

SELECT * FROM items

id | item      | position
---|-----------|----------
1 | USB cable |        0
2 | SD card   |        4
3 | Mouse     |        2
4 | Keyboard  |        0
5 | Monitor   |        3

對該表進行排序得到以下結果：

SELECT * FROM items ORDER BY position

id | item      | position
---|-----------|----------
4 | Keyboard  |        0
1 | USB cable |        0
3 | Mouse     |        2
5 | Monitor   |        3
2 | SD card   |        4

現在我想更新表格並將訂單保存在位置列中：

SELECT * FROM items

id | item      | position
---|-----------|----------
4 | Keyboard  |        1
1 | USB cable |        2
3 | Mouse     |        3
5 | Monitor   |        4
2 | SD card   |        5

這可以通過單個查詢來完成，還是我必須手動遍歷所有行並進行手動更新？

如果訂單沒有完全定義（例如上面的 USB 電纜和鍵盤），我只是隨意決定了訂單。

UPDATE
  T1
SET
  position = T2.rn
FROM
  myTable T1
  JOIN
  (
  SELECT
     id,
     ROW_NUMBER() OVER (ORDER BY position) AS rn
  FROM
     myTable
  ) T2 ON T1.id = T2.id

注意：“鍵盤”和“USB 數據線”的順序是任意的。他們都有位置= 0

要基於項目的搶七位置，請添加輔助排序

     ROW_NUMBER() OVER (ORDER BY position, item) AS rn

如果您有重複的位置，項目對這也將是任意的……

以下是如何在 MySQL 中實現這一點：

set @x = 0;
update items A
inner join
(
   select @x:=@x+1 newposition,id
   from items
   order by position
) B using (id)
set A.position=B.newposition;

我使用了問題中的範例數據，將其載入到筆記型電腦上的 MySQL 5.5.15 中，然後執行了這兩行。結果如下：

mysql> use test
Database changed
mysql> drop table if exists items;
Query OK, 0 rows affected (0.01 sec)

mysql> create table items
   -> (
   ->     id int not null auto_increment,
   ->     item varchar(20),
   ->     position int,
   ->     primary key(id)
   -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into items (item,position) values
   -> ('USB cable',0),('SD Card',4),
   -> ('Mouse',2),('Keyboard',0),('Monitor',3);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from items;
+----+-----------+----------+
| id | item      | position |
+----+-----------+----------+
|  1 | USB cable |        0 |
|  2 | SD Card   |        4 |
|  3 | Mouse     |        2 |
|  4 | Keyboard  |        0 |
|  5 | Monitor   |        3 |
+----+-----------+----------+
5 rows in set (0.00 sec)

mysql> select * from items order by position;
+----+-----------+----------+
| id | item      | position |
+----+-----------+----------+
|  1 | USB cable |        0 |
|  4 | Keyboard  |        0 |
|  3 | Mouse     |        2 |
|  5 | Monitor   |        3 |
|  2 | SD Card   |        4 |
+----+-----------+----------+
5 rows in set (0.00 sec)

mysql> set @x = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> update items A
   -> inner join
   -> (
   ->     select @x:=@x+1 newposition,id
   ->     from items
   ->     order by position
   -> ) B using (id)
   -> set A.position=B.newposition;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from items;
+----+-----------+----------+
| id | item      | position |
+----+-----------+----------+
|  1 | USB cable |        1 |
|  2 | SD Card   |        5 |
|  3 | Mouse     |        3 |
|  4 | Keyboard  |        2 |
|  5 | Monitor   |        4 |
+----+-----------+----------+
5 rows in set (0.00 sec)

mysql> select * from items order by position;
+----+-----------+----------+
| id | item      | position |
+----+-----------+----------+
|  1 | USB cable |        1 |
|  4 | Keyboard  |        2 |
|  3 | Mouse     |        3 |
|  5 | Monitor   |        4 |
|  2 | SD Card   |        5 |
+----+-----------+----------+
5 rows in set (0.00 sec)

引用自：https://dba.stackexchange.com/questions/5146