SQL CASE 查詢優先級

我正在做一個 pokemon sql 表並創建了一個名為 capacity_difference 的新列來確定表中每個 pokemon 的可收集性。但是，當我想使用 CASE 查詢對它們進行分類時，結果只顯示每個日誌的 ELSE 條件。有人可以告訴我如何修復查詢嗎？

我的查詢如下：

ALTER TABLE pokemon add capacity_difference INTEGER;

SELECT name, type_1, type_2, HP, (attack - defense) as capacity_difference from pokemon; 

SELECT name, type_1, type_2, HP, (attack - defense) as capacity_difference, 
 CASE 
   when capacity_difference > 90 then "collect asap"
   when  capacity_difference < 90 and capacity_difference > 50 then "good"
   when capacity_difference > 0 and capacity_difference < 50 then "okay"
   when capacity_difference > -10 and capacity_difference < 0 then "bad"
 ELSE "worse"
 END AS Collectability
FROM pokemon;

問題是在評估您的案例表達式時，不知道重命名（（攻擊 - 防禦）為 capacity_difference ）。即您的案例表達式正在評估capacity_difference您尚未分配值的列。我建議您擺脫該列，並執行以下操作：

SELECT name
    , type_1
    , type_2
    , HP
    , CASE when capacity_difference > 90 
           then "collect asap" 
           when capacity_difference < 90 and capacity_difference > 50 
           then "good" 
           when capacity_difference > 0 and capacity_difference < 50 
           then "okay" 
           when capacity_difference > -10 and capacity_difference < 0 
           then "bad" 
           ELSE "worse" 
      END AS Collectability 
FROM (
   SELECT (attack - defense) as capacity_difference
        ,  name
        , type_1
        , type_2
        , HP
   FROM pokemon
) AS t;

引用自：https://dba.stackexchange.com/questions/271005