【SQL】【MYSQL/SQL SERVER】UPDATE更新排名列（通过查询数据更新原表字段）

原创已于 2025-11-20 18:05:25 修改 · 2k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #java #数据库

于 2023-05-11 10:15:42 首次发布

文章演示了如何使用SQL的UPDATE语句结合INNERJOIN操作来更新数据表中某个字段的值，特别是计算每个记录的排名（rankData）字段。首先创建了一个名为student的学生数据表，然后通过联表查询确定每个学生的排名，最后更新student表中的rankData字段，使其根据ID降序显示排名。

UPDATE更新原理为行更新，即查询当前行内数据之间的数据更新，所以我们要做的便是将需要更新的数据字段和更新数据源字段放在同一行

创建示例数据

INSERT INTO student (id,stuName,sex,score,rankData) VALUES
	 (1,'张三','男','64',''),
	 (2,'李四','女','57',''),
	 (3,'王五','男','74',''),
	 (4,'赵六','女','84',''),
	 (5,'孙七','男','51',''),
	 (6,'钱八','女','89',''),
	 (7,'周九','女','58','');

查询

select * from student

结果:

联表查询

select * from student a
INNER JOIN (
select  t.Id,@i := @i + 1 as rankNum
	from
	(select @i := 0 ) r,	
	(select 
		* 
	from 
		student
	order by 
		Id desc 
	)as t) t1
on a.id = t1.id

结果：

通过查询数据更新student表中的rankData字段

MYSQL:

update student a
INNER JOIN (
select  t.Id,@i := @i + 1 as rankNum
	from
	(select @i := 0 ) r,	
	(select 
		* 
	from 
		student
	order by 
		Id asc 
	)as t) t1
on a.id = t1.id 

 set rankData = rankNum

select * from student

SQLSERVER:

update a
set a.rankData = t1.rankNum
student a
INNER JOIN (
select  t.Id,@i := @i + 1 as rankNum
	from
	(select @i := 0 ) r,	
	(select 
		* 
	from 
		student
	order by 
		Id asc 
	)as t) t1
on a.id = t1.id;



select * from student;

结果：