添加点到SQL服务器中的排名 [英] Add points to ranking in SQL server

问题描述

我想在数据库中为竞争对手的排名添加某些点数，例如

I would like to add certain points to rankings of competitors in a database for example

Avg              Placing/rank     points
  95              1st             140
  90             2nd              130
  85             3rd              120
  83             4th              110
  76             5th              100
  75             6th               90
  75             6th               90
  73             8th               80
  72             9th               70
  71             10th              60

如果任何一个领带有平局然后放置相应的竞争对手将收到相同的积分，放置和放置跳到下一个，如上面的例子中的第8个。



请协助



我尝试了什么：



排名，行排名，排名功能，怎么样排在数据库

if there is a tie for any of the placing then the respective competitors will recieve the same points for the placing and placing skips to the next as in placing 8th in the example above.

Please assist

What I have tried:

rank, row ranks,ranking function, how to rank in a database

推荐答案

看看：排名函数（Transact-SQL）| Microsoft Docs [ ^ ]。你需要使用 RANK 函数，例如：

Take a look at: Ranking Functions (Transact-SQL) | Microsoft Docs[^]. You need to use RANK function, for example:

SELECT Src.points, RANK() OVER(ORDER BY Src.Points DESC) AS Placing
FROM (
	SELECT 140 AS points UNION ALL
	SELECT 130 AS points UNION ALL
	SELECT 120 AS points UNION ALL
	SELECT 110 AS points UNION ALL
	SELECT 100 AS points UNION ALL
	SELECT 90 AS points UNION ALL
	SELECT 90 AS points UNION ALL
	SELECT 80 AS points UNION ALL
	SELECT 70 AS points UNION ALL
	SELECT 60 AS points
) AS Src

结果：

Result:

points	Placing
140	1
130	2
120	3
110	4
100	5
90	6
90	6
80	8
70	9
60	10

你可以试试这个：



在每个等级的单独表格中都有硬编码点。并创建了一个临时表来首先找到排名。然后内部连接临时表与点表根据等级映射点。

you may try this:

Have hardcoded points in a seperate table for each rank. And created a temp table to find the rank first. Then inner join temp table with points table to map point based on rank.

declare  @avgrank table(avg1 int ,rank1 int  )
 insert into  @avgrank( avg1) values (95)
 insert into  @avgrank( avg1) values (90)
 insert into  @avgrank( avg1) values (85)
 insert into  @avgrank( avg1) values (83)
 insert into  @avgrank( avg1) values (76)
 insert into  @avgrank( avg1) values (75)
 insert into  @avgrank( avg1) values (75)
 insert into  @avgrank( avg1) values (73)
 insert into  @avgrank( avg1) values (72)
 insert into  @avgrank( avg1) values (71)


 declare @points table(rank1 int ,point int )
 insert into  @points values (1,140)
 insert into  @points values (2,130)
 insert into  @points values (3,120)
 insert into  @points values (4,110)
 insert into  @points values (5,100)
 insert into  @points values (6,90)
 insert into  @points values (7,80)
 insert into  @points values (8,70)
 insert into  @points values (9,60)
 


 IF OBJECT_ID('tempdb..#tempavg') IS NOT NULL
    DROP TABLE #tempavg

 SELECT avg1, RANK() OVER (ORDER BY avg1 DESC) AS Placing  
  INTO #tempavg FROM @avgrank a 

SELECT avg1,Placing, p.point FROM #tempavg ta INNER JOIN @points p ON ta.Placing = p.rank1

这篇关于添加点到SQL服务器中的排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！