mysql按顺序更新,mysql – 根据另一个查询的排序顺序更新列

我正在尝试将arbitrarily ordered records添加到数据库中,以便它们可以按ui排序(或者当我查询数据库时).我的问题是我已经有了列表,我需要添加一个基于字母顺序的默认排序.我想我应该能够通过子查询或合并来做到这一点,但我无法正确地得到它.我在MySQL上这样做,所以我希望它可以在数据库级别上完成.

解决方法:

让我们从一个表开始,将数据放入其中,添加一个排序顺序列

USE test

DROP TABLE IF EXISTS ordered_names;

CREATE TABLE ordered_names

(

id int not null auto_increment,

firstname VARCHAR(20),

lastname VARCHAR(20),

PRIMARY KEY (id),

KEY name_ndx (lastname,firstname)

);

INSERT INTO ordered_names (firstname,lastname) VALUES

('pamela','edwards'),('rolando','edwards'),('diamond','edwards');

ALTER TABLE ordered_names ADD COLUMN sort_order INT DEFAULT NULL;

SELECT * FROM ordered_names;

让我们实际运行：

mysql> USE test

Database changed

mysql> DROP TABLE IF EXISTS ordered_names;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE ordered_names

-> (

-> id int not null auto_increment,

-> firstname VARCHAR(20),

-> lastname VARCHAR(20),

-> PRIMARY KEY (id),

-> KEY name_ndx (lastname,firstname)

-> );

Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO ordered_names (firstname,lastname) VALUES

-> ('pamela','edwards'),('rolando','edwards'),('diamond','edwards');

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE ordered_names ADD COLUMN sort_order INT DEFAULT NULL;

Query OK, 3 rows affected (0.17 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM ordered_names;

+----+-----------+----------+------------+

| id | firstname | lastname | sort_order |

+----+-----------+----------+------------+

| 1 | pamela | edwards | NULL |

| 2 | rolando | edwards | NULL |

| 3 | diamond | edwards | NULL |

+----+-----------+----------+------------+

3 rows in set (0.00 sec)

mysql>

接下来,让我们填充排序顺序列：

SET @x = 0;

UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;

SELECT * FROM ordered_names;

让我们运行：

mysql> SET @x = 0;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3 Changed: 3 Warnings: 0

mysql> SELECT * FROM ordered_names;

+----+-----------+----------+------------+

| id | firstname | lastname | sort_order |

+----+-----------+----------+------------+

| 1 | pamela | edwards | 2 |

| 2 | rolando | edwards | 3 |

| 3 | diamond | edwards | 1 |

+----+-----------+----------+------------+

3 rows in set (0.00 sec)

mysql>

好的,到目前为止,让我们再添加四(4)个人并重新排序：

INSERT INTO ordered_names (firstname,lastname) VALUES

('javonne','washington'),('richard','washington'),

('carlik','washington'),('dominique','edwards');

SET @x = 0;

UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;

SELECT * FROM ordered_names;

让我们运行：

mysql> INSERT INTO ordered_names (firstname,lastname) VALUES

-> ('javonne','washington'),('richard','washington'),

-> ('carlik','washington'),('dominique','edwards');

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> SET @x = 0;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;

Query OK, 6 rows affected (0.00 sec)

Rows matched: 7 Changed: 6 Warnings: 0

mysql> SELECT * FROM ordered_names;

+----+-----------+------------+------------+

| id | firstname | lastname | sort_order |

+----+-----------+------------+------------+

| 1 | pamela | edwards | 3 |

| 2 | rolando | edwards | 4 |

| 3 | diamond | edwards | 1 |

| 4 | javonne | washington | 6 |

| 5 | richard | washington | 7 |

| 6 | carlik | washington | 5 |

| 7 | dominique | edwards | 2 |

+----+-----------+------------+------------+

7 rows in set (0.00 sec)

mysql>

试试看 ！！！

标签：mysql,update

来源： https://codeday.me/bug/20190807/1605557.html