sql—连接两个表，根据它们的列运行计算，并根据结果设置列

hlswsv35 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(249)

为什么我的查询在此之后中断？我基本上是基于两个表的列运行计算，然后在rn是1到5000之间的某个值时设置它们的pvplevel。
设置pvpranking.pvplevel=case

USE DNWorld
GO

WITH cte AS (
  SELECT PVPScores.PVPWin, PVPScores.PVPLose, PVPRanking.Death, PVPRanking.[Kill], PVPRanking.PVPExp,
RANK() OVER (ORDER BY (((PVPScores.PVPWin * 1) + (PVPRanking.[Kill] * 0.2) + (PVPRanking.PvpExp * 0.002)) - ((PVPScores.PVPLose * 0.5) + (PVPRanking.Death * 0.1) + (PVPScores.PvPGiveUp * 0.5))) DESC, PVPScores.CharacterID DESC ) AS rn
  FROM PvPRanking
INNER JOIN PVPScores
      ON PVPRanking.CharacterID = PVPScores.CharacterID

)
UPDATE cte
SET PVPRanking.PVPLevel = CASE
  WHEN rn = 1 THEN 26 -- #1 Rank Champion
  WHEN rn BETWEEN 2 AND 10 THEN 25 -- Reita
    WHEN rn BETWEEN 11 and 20 THEN 24 -- Warlord
    WHEN rn BETWEEN 21 and 30 then 23 -- Slayer
    WHEN rn BETWEEN 31 and 40 then 22 -- WATCH OUT
    WHEN rn BETWEEN 41 and 50 then 21 -- Royalty
    WHEN rn BETWEEN 51 and 60 then 20 -- Knight of Justice
    WHEN rn BETWEEN 61 and 70 then 19 -- Furious Fiend
    WHEN rn BETWEEN 71 and 85 then 18 -- Devil of PVP
    WHEN rn BETWEEN 86 and 100 then 17 -- God Of PvP
  WHEN rn BETWEEN 101 and 125 then 16 -- Death God
  WHEN rn BETWEEN 126 and 150 then 15 -- Master
  WHEN rn BETWEEN 151 and 175 then 14 -- Unpredictable Demon
  WHEN rn BETWEEN 176 and 250 then 13 -- Demon
  WHEN rn BETWEEN 251 and 300 then 12 -- Crazy Fiend
  WHEN rn BETWEEN 301 and 400 then 11 -- Fiend
  WHEN rn BETWEEN 401 and 500 then 10 -- Bloodthirsty
  WHEN rn BETWEEN 501 and 600 then 9 -- Kavalan
  WHEN rn BETWEEN 601 and 700 then 8 -- Keepo
  WHEN rn BETWEEN 701 and 800 then 7 -- Senior PvP Tactician
  WHEN rn BETWEEN 801 and 900 then 6 -- PvP Tactician
  WHEN rn BETWEEN 901 and 1000 then 5 -- Senior Apprentice
  WHEN rn BETWEEN 1001 and 1100 then 4 -- Apprentice
  WHEN rn BETWEEN 1101 and 1500 then 3 -- Lesser Novice
  WHEN rn BETWEEN 1501 and 2000 then 2 -- Novice
  WHEN rn BETWEEN 2000 and 5000 then 1 -- Egg

  ELSE 1

END
WHERE rn <= 5000;

sql

来源：https://stackoverflow.com/questions/62164042/join-two-tables-run-a-calculation-based-on-their-columns-and-set-a-column-base

1条答案

按热度按时间

a7qyws3x1#

似乎您只需要包括 PVPLevel 在cte的选择列表中：

WITH cte AS (
    SELECT ps.PVPWin, ps.PVPLose, pr.Death, pr.[Kill], pr.PVPExp, pr.PVPLevel,
           RANK() OVER (ORDER BY (((ps.PVPWin * 1) + (pr.[Kill] * 0.2) + (pr.PvpExp * 0.002)) - ((ps.PVPLose * 0.5) + (pr.Death * 0.1) + (ps.PvPGiveUp * 0.5))) DESC, ps.CharacterID DESC ) AS rn
    FROM PvPRanking pr
    INNER JOIN PVPScores ps ON pr.CharacterID = ps.CharacterID
)

然后，按原样继续当前的更新查询。请注意，更新本身可以简化一点，因为范围检查总是从上到下递增：

UPDATE cte
SET PVPLevel = CASE WHEN rn = 1 THEN 26    -- #1 Rank Champion
                    WHEN rn <= 10 THEN 25  -- Reita
                    WHEN rn <= 20 THEN 24  -- Warlord
                    WHEN rn <= 30 THEN 23  -- Slayer
                    WHEN rn <= 40 THEN 22  -- WATCH OUT
                    WHEN rn <= 50 THEN 21  -- Royalty
                    WHEN rn <= 60 THEN 20  -- Knight of Justice
                    WHEN rn <= 70 THEN 19  -- Furious Fiend
                    WHEN rn <= 85 THEN 18  -- Devil of PVP
                    WHEN rn <= 100 THEN 17 -- God Of PvP
                    WHEN rn <= 125 THEN 16 -- Death God
                    WHEN rn <= 150 THEN 15 -- Master
                    WHEN rn <= 175 THEN 14 -- Unpredictable Demon
                    WHEN <= 250 THEN 13    -- Demon
                    WHEN <= 300 THEN 12    -- Crazy Fiend
                    WHEN <= 400 THEN 11    -- Fiend
                    WHEN <= 500 THEN 10    -- Bloodthirsty
                    WHEN <= 600 THEN 9     -- Kavalan
                    WHEN <= 700 THEN 8     -- Keepo
                    WHEN <= 800 THEN 7     -- Senior PvP Tactician
                    WHEN rn <= 900 THEN 6  -- PvP Tactician
                    WHEN rn <= 1000 THEN 5 -- Senior Apprentice
                    WHEN rn <= 1100 THEN 4 -- Apprentice
                    WHEN rn <= 1500 THEN 3 -- Lesser Novice
                    WHEN rn <= 2000 THEN 2 -- Novice
                    WHEN rn <= 5000 THEN 1 -- Egg
                    ELSE 1 END
WHERE rn <= 5000;

赞(0）回复(0）举报 2021-07-26

我来回答

sql—连接两个表，根据它们的列运行计算，并根据结果设置列

1条答案

相关问题

热门标签

最新问答