(目录)
需求说明
- 新加一个字段
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 |
+----+--------+-----+