主页 / dba / 问题 / 271005
Accepted
Lynn
Asked: 2020-07-16 07:51:43 +0800 CST

SQL CASE 查询优先级

  • 772

我正在做一个 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;
query syntax

2 个回答

  1. Best Answer

    问题是在评估您的案例表达式时，不知道重命名（（攻击 - 防御）为 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;
    • 0

  2. 使用 mysql 和 mariadb，你可以做到

    CrEATE Table pokemon (name VARCHAR(19), type_1 VARCHAR(19), type_2 VARCHAR(19), HP INT, attack INT, defense INT);

    INSERT INTO pokemon VALUES ('a','fire','water',20,4,2),('b','fire','water',20,67,2);

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

    姓名 | 类型_1 | 类型_2 | 惠普 | 容量差异 | 可收藏性
:--- | :----- | :----- | -: | ------------------: | :-------------
一个 | 火灾| 高分辨率照片| CLIPARTO 水| 20 | 2 | 好的          
乙 | 火灾| 高分辨率照片| CLIPARTO 水| 20 | 65 | 好的

    db<>在这里摆弄

    • 0

相关问题