update t
set
Tag = 'f'
from
tab_1 t
where
t.id not in(select top 2 id from tab_1 where Name=t.Name order by level desc)
set
Tag = 'f'
from
tab_1 t
where
t.id not in(select top 2 id from tab_1 where Name=t.Name order by level desc)
declare @t table (id int,name varchar(20),level int,tag varchar(20))
insert into @t select 1,'a',3,'t'
union all select 2,'a',7,'t'
union all select 3,'a',9,'t'
union all select 4,'a',4,'t'
union all select 5,'b',14,'t'
union all select 6,'b',13,'t'
union all select 7,'c',13,'t'
union all select 8,'c',13,'t'
union all select 9,'c',15,'t'
update t
set t.tag='f'
from @t t where t.id in (select top 2 y.id from @t y where y.name=t.name order by y.level DESC)select * from @t
id name level tag
----------- -------------------- ----------- --------------------
1 a 3 t
2 a 7 f
3 a 9 f
4 a 4 t
5 b 14 f
6 b 13 f
7 c 13 t
8 c 13 f
9 c 15 f
-------------------------------
1 a 3 f 4
2 a 7 t 2
3 a 9 t 1
4 a 4 f 3
5 b 14 t 2
6 b 13 t 1
7 c 13 f 2
8 c 13 f 3
9 c 15 t 1说明: 添加了一个字段,这个字段的得来是按照 Name 分组后,按照level 排序,把序号写在Num字段。 都有那些写法,谢谢回答