sql：递减/递减更新一列字段值

(目录)

需求说明

• 新加一个字段age
• 按照id逆序（由大到小）排序递增设置age字段值

即：

• id最大的那行的age字段值设置为1；
• id最小的那行的age字段值设置为最大值

最终实现效果

select * from tb_student;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | Tom    |   5 |
|  2 | Jack   |   4 |
|  3 | Steve  |   3 |
|  4 | Yellow |   2 |
|  5 | Green  |   1 |
+----+--------+-----+

环境准备

select version();
+-----------+
| version() |
+-----------+
| 8.0.29    |
+-----------+

初始化数据表

CREATE TABLE `tb_student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20)  NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);


INSERT INTO `tb_student`(`id`, `name`) VALUES (1, 'Tom');
INSERT INTO `tb_student`(`id`, `name`) VALUES (2, 'Jack');
INSERT INTO `tb_student`(`id`, `name`) VALUES (3, 'Steve');
INSERT INTO `tb_student`(`id`, `name`) VALUES (4, 'Yellow');
INSERT INTO `tb_student`(`id`, `name`) VALUES (5, 'Green');

查看数据

select * from tb_student;
+----+--------+
| id | name   |
+----+--------+
|  1 | Tom    |
|  2 | Jack   |
|  3 | Steve  |
|  4 | Yellow |
|  5 | Green  |
+----+--------+

需求实现

增加列

ALTER TABLE `tb_student` 
ADD COLUMN `age` int(0) NOT NULL DEFAULT 0 AFTER `name`;

-- 此时的数据
select * from tb_student;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | Tom    |   0 |
|  2 | Jack   |   0 |
|  3 | Steve  |   0 |
|  4 | Yellow |   0 |
|  5 | Green  |   0 |
+----+--------+-----+

关键SQL语句

SET @rownum = 1;
SELECT @rownum := @rownum + 1 AS num;
+------+
| num  |
+------+
|    2 |
+------+

递减更新age字段数据

SET @rownum = 0;

UPDATE tb_student 
SET age = ( SELECT @rownum := @rownum + 1 AS num )
ORDER BY id desc;

更新结果

select * from tb_student;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | Tom    |   5 |
|  2 | Jack   |   4 |
|  3 | Steve  |   3 |
|  4 | Yellow |   2 |
|  5 | Green  |   1 |
+----+--------+-----+