根據年齡組的獲勝者結果計算每個參與者的分數

各年齡組比賽階段的獲勝者是跑得最快的參賽者。相應組中的每個參與者都會獲得每個階段的積分。獲得的分數是通過將小組獲勝者的時間除以參賽者的時間并乘以1000計算得出的。總分由5個最佳階段的分數之和計算得出。

我有這樣的參與者資料。

AgeGroup   Start_Nr   First_name   Last_Name    Distance_result
-------------------------------------------------------------
M30          5         John        Lala           180
M35          1         Paul        Baba           175
M35          6         Patric      Ziza           192
M30          3         Peter       Mikel          190
S30          2         Sandra      Lilua          250
S30          4         Julia       Parker         260

我想像這樣計算和顯示點

Age_Group|Start_Nr|First_name|Last_Name|Distance_result|Points
----------------------------------------------------------
M30         5         John        Lala           180          1000
M30         3         Peter       Mikel          190           947
M35         6         Patric      Ziza           175          1000
M35         1         Paul        Babas          185           946
S30         2         Sandra      Lilua          250          1000
S30         4         Julia       Parker         260           962

年齡組中的每個獲勝者獲得 1000 分，該年齡組中的其他人獲得積分計算 -> (MIN(Distance_result)/(Distance_result) * 1000)

SELECT  [Age_group],
    [Start_number] ,
    [First_name],
    [Last_name],
    [Stage_Nr],
    [Distance_result], (180/[Distance_result]*1000) AS Points,
    DENSE_RANK() OVER (PARTITION BY [Age_group] ORDER BY [Distance_result] ASC) AS PlaceRank
FROM [ParticipantDetails].[dbo].[ParticipantForm]

如何在每個年齡組中使用 MIN(Distance_result) 進行積分計算？
在我的解決方案中，我只能手動插入 MIN(Distance_result) 來計算點數，但即便如此，它對其他年齡組也不正確。每個年齡段都有不同的最佳成績。

uj5u.com熱心網友回復：

在我的示例中，我用子查詢解決了您的問題。我還添加了 round 以洗掉小數，在此之前我添加了 cast 以獲得一些結果，否則我只會收到 1 或 0，或者您可以這樣做：p1.[Distance_result] 而不是 cast...

SELECT  [AgeGroup],
        [Start_Nr] ,
        [First_name],
        [Last_name],
        [Distance_result], 
        (select round(min(p2.Distance_result)/p1.[Distance_result] * 1000, 0) AS Points 
         from ParticipantForm p2
         where p2.AgeGroup = p1.AgeGroup
         group by p2.AgeGroup) as Points, 
        DENSE_RANK() OVER (PARTITION BY [AgeGroup] ORDER BY [Distance_result] ASC) AS PlaceRank
FROM [ParticipantForm] p1

在這里你可以看到演示。

uj5u.com熱心網友回復：

您可以使用子查詢來獲取最佳時間并將表加入其中。
從性能的角度來看，最好在連接中使用僅運行一次的子查詢，而不是為每一行運行的行級子查詢。我們通過在除法之前乘以 1000
來避免轉換為浮點數并使用round() 。

    SELECT  
    [Age_group],
    [Start_number] ,
    [First_name],
    [Last_name],
    [Stage_Nr],
    [Distance_result], 
    (b.best*1000)/[Distance_result AS Points,
    DENSE_RANK() OVER (PARTITION BY [Age_group] ORDER BY [Distance_result] ASC) AS PlaceRank
    FROM [ParticipantDetails].[dbo].[ParticipantForm] 
    JOIN ( SELECT  [Age_group] AgeGroup,
                   MIN([Distance_result]) AS best 
           FROM [ParticipantDetails].[dbo].[ParticipantForm] 
           GROUP BY [Age_group]) AS b 
    ON b.[AgeGroup] = [ParticipantForm].[Age_group];

uj5u.com熱心網友回復：

嘗試計算由 Age_Group 劃分的最小 Distance_Result。然后計算分數。

 WITH cte AS (
    SELECT * 
           , DENSE_RANK() OVER (PARTITION BY [Age_Group] ORDER BY [Distance_Result] ASC) AS Place_Rank
           , MIN(Distance_Result) OVER (PARTITION BY [Age_Group] ORDER BY [Distance_Result] ASC) AS Min_Distance
    FROM  [ParticipantForm]
 )
 SELECT [Age_group]
        , [First_Name]
        , [Last_Name]
        , [Start_Number]
        , [Distance_Result]
        , CAST(ROUND( [Min_Distance] * 1000.0 / [Distance_Result], 0 ) AS INT) AS Points
 FROM   cte

結果：

db<>在這里擺弄

標籤：sql tsql

上一篇：使用STUFF函式使用嵌套物件形成json

下一篇：SQLServer'AS'別名意外語法