sqlserver 通过Row_number 排序后修改内容

1.先创建表

create table CS(
	xh varchar(100),
	core int,
	cj varchar(10)
)

2.插入数据并且全部设为合格

declare @i int
set @i=1
while @i<=100
begin
	insert CS 
	select @i+'',(select round(150*rand(),0)),'合格'
	set @i=@i+1
end

3成绩从小到大排序后取前20序号名不合格

update CS set cj='不合格' where xh in(
select xh from(
select *,ROW_NUMBER() over (order by core asc) as rn from CS ) a  where a.rn<=20
)

4.然后感觉太多的情况下只取前40，在合格中成绩从小到大排序后取前40序号的不合格

update CS set CJ='不合格' where  xh in (select xh from(select *,ROW_NUMBER() over (order by core ) as rn from CS where CJ='合格')a where rn<=40)

5.或者直接取全部成绩从小到大排序后取序号前60的不合格

update CS set cj='不合格' where xh in(
select xh from(
select *,ROW_NUMBER() over (order by core asc) as rn from CS ) a  where a.rn<=60
)