select Score=case when Score<100 then Score+10 else Score end from A
select id=(select count(1)+1 from a a where score>a.score),score from a aa
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([Score] int) insert [TB] select 100 union all select 80 union all select 60 union all select 70--2005 select id=Row_Number() over (order by [Score] desc), Score=[Score] from [TB]/* id Score -------------------- ----------- 1 100 2 80 3 70 4 60(4 行受影响)*/--2000 select id=(select count(1) from TB where T.[Score]<=[Score]), [Score] from [TB] t/* id Score ----------- ----------- 1 100 2 80 4 60 3 70(4 行受影响)*/drop table TB
select id=row_number() over(order by score desc),score from A order by score desc
有相同的也无所谓 加个distinct就可以了
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([id] int,[point] int,[name] varchar(4)) insert [TB] select 1,100,'张三' union all select 2,80,'李四' union all select 3,80,'王五' union all select 4,75,'杨六' union all select 5,75,'方七'select id=(select count([point])+1 from TB where T.[point]<[point]),point,name from TB t/* id point name ----------- ----------- ---- 1 100 张三 2 80 李四 2 80 王五 4 75 杨六 4 75 方七(5 行受影响) */drop table TB--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([id] int,[point] int,[name] Nvarchar(4)) insert [TB] select 1,100,N'张三' union all select 2,80,N'李四' union all select 3,80,N'王五' union all select 4,75,N'杨六' union all select 5,75,N'方七'select id=(select count(distinct point) from TB where T.[point]<=[point]), point, [name] from TB t /* id point name ----------- ----------- ---- 1 100 张三 2 80 李四 2 80 王五 3 75 杨六 3 75 方七(5 個資料列受到影響) */
from a aa
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([Score] int)
insert [TB]
select 100 union all
select 80 union all
select 60 union all
select 70--2005
select id=Row_Number() over (order by [Score] desc),
Score=[Score]
from [TB]/*
id Score
-------------------- -----------
1 100
2 80
3 70
4 60(4 行受影响)*/--2000
select id=(select count(1) from TB where T.[Score]<=[Score]),
[Score]
from [TB] t/*
id Score
----------- -----------
1 100
2 80
4 60
3 70(4 行受影响)*/drop table TB
有相同的也无所谓 加个distinct就可以了
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[point] int,[name] varchar(4))
insert [TB]
select 1,100,'张三' union all
select 2,80,'李四' union all
select 3,80,'王五' union all
select 4,75,'杨六' union all
select 5,75,'方七'select id=(select count([point])+1 from TB where T.[point]<[point]),point,name
from TB t/*
id point name
----------- ----------- ----
1 100 张三
2 80 李四
2 80 王五
4 75 杨六
4 75 方七(5 行受影响)
*/drop table TB--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[point] int,[name] Nvarchar(4))
insert [TB]
select 1,100,N'张三' union all
select 2,80,N'李四' union all
select 3,80,N'王五' union all
select 4,75,N'杨六' union all
select 5,75,N'方七'select id=(select count(distinct point) from TB where T.[point]<=[point]),
point,
[name]
from TB t
/*
id point name
----------- ----------- ----
1 100 张三
2 80 李四
2 80 王五
3 75 杨六
3 75 方七(5 個資料列受到影響)
*/