4

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

 1 year ago
source link: https://blog.51cto.com/mouday/5820709
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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

精选 原创

彭世瑜 2022-11-03 18:16:57 ©著作权

文章标签 sql 字段 数据 文章分类 MySQL 数据库 yyds干货盘点 阅读数254

  • 新加一个字段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 |
+----+--------+-----+
  • 打赏
  • 赞
  • 收藏
  • 评论
  • 分享
  • 举报

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK