根据相同和其他字段更新每个字段 [英] Update every field depending on the same and other fields

问题描述

我是使用 SQL Server 2012 的用户，需要更新查询方面的帮助.

I'm an ameture using SQL server 2012 and need help with an update query.

我有一个显示结果的 SQL 表.每个结果都是唯一的，通过活动日期、竞赛 ID 和结果排名(第 1 2 3 等)

I have a SQL table showing results. Every result is unique through the date of the event, the ContestID and the rank of the result (1st 2nd 3rd etc.)

每个结果都会根据他们在赛事中的表现分配积分.

Every result has allocated points depending on how well they did in the event.

最后一名、第 4、第 3、第 2 和第 1 名的积分相同，但最后和第 4 名之间的积分取决于有多少结果.

Last place, 4th, 3rd, 2nd and 1st have the same points for each event but points between last and 4th are calculated depending on how many results there are.

例如7 个结果:

旧
第 1 - 150 分
第二 - 125pts
第三名 - 100 分
-----------------
第 4 - 95 分
第 5 - 90 分
第 6 名 - 85 分
7th(最后) - 80pts

OLD
1st - 150pts
2nd - 125pts
3rd - 100pts
-----------------
4th - 95pts
5th - 90pts
6th - 85pts
7th(Last) - 80pts

新
第 1 - 300 分
第二 - 250pts
第三 - 225pts
第 4 名 - 200 分
---------------------
第五 - 166.666pts
第六 - 133.333pts
7th(最后) - 100pts

NEW
1st - 300pts
2nd - 250pts
3rd - 225pts
4th - 200pts
--------------------
5th - 166.666pts
6th - 133.333pts
7th(Last) - 100pts

此积分系统是旧系统更新，需要应用.任何人都可以帮助查询来计算第 5 个 - 最后一个的结果.谢谢

This points system is update from an old system and needs to be applied. Can anyone help with the query to calculate the results which are 5th - last. Thanks

推荐答案

这毕竟是一道数学题，我只需要一个合适的算法.

It was a math problem after all and i just needed an appropriate algorithm.

UPDATE RESULTS

/*
    The old system started on 80, the new starts on 100 (-80)&(+100).
    The difference between Last and 3rd/4th is now 100 instead of 20 (*5)
*/
SET Points = ((Points - 80)*5)+100

/*Select the appropriate data needed to edit*/
WHERE Position > 4
AND ContestID = 1
OR ContestID = 2
OR ContestID = 3

前 4 名每次都有相同的分数，因此可以对下面的结果进行单独更新.

The top 4 have the same amount of points every time so a separate update can be done for the results below.

这篇关于根据相同和其他字段更新每个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！