排名sqlserver记录

问题描述

你好.我有以下数据库表

(sampleTable)

平均标记位置
97
96 NULL
25 NULL
36 NULL
78.25 NULL
96 NULL

我设法编写了一个选择所有
的C#命令
sampleTable中的记录并将它们安排在DESC中

根据平均标记"列的顺序.我有

设法根据
更新了位置列
在AverageMark中得分.例如97和96在位置2

等等.那我该如何考虑存在

是紧随位置2的两个数字(96).

因此，由于这条领带，我确定位置3

不应该存在.但改为位置4.我怎么

Hi there. I have the following database table

(sampleTable)

AverageMark Position
97
96 NULL
25 NULL
36 NULL
78.25 NULL
96 NULL

I Have Managed to write a C# Command that selects all

the records from sampleTable and arrange them in DESC

Order acccording to the AverageMark Column. I have

managed to update the position column according to

scores in the AverageMark. E.g 97 and 96 is position 2

and so on. How can i then consider the fact that there

are two figures (96) that follow in position 2.

Therefore because of this tie, i am sure position 3

should not exist. But instead postion 4. how can i

achieve this?

推荐答案

if object_id('tempdb..#temp') is not null
    drop table #temp

create table #temp
(
AverageMark decimal(10,2),
Position    int
)


insert into #temp(AverageMark)values(97)
insert into #temp(AverageMark)values(96)
insert into #temp(AverageMark)values(25)
insert into #temp(AverageMark)values(36)
insert into #temp(AverageMark)values(78.25)
insert into #temp(AverageMark)values(96)

-- 1)  SQL2005 and up
select  AverageMark,
        rank() over(order by AverageMark desc) as Position
from    #temp
order by AverageMark desc

-- 2)  
select  AverageMark,
        (select count(*) from #temp where AverageMark > a.AverageMark) + 1  as Position
from    #temp a
order by AverageMark desc

这篇关于排名sqlserver记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！