mysql - CASE END AS 导致服务器错误

Question

我花了半天时间想弄清楚这个问题，但仍然感到困惑。我正在查询一张大表。当我在 Case END 后没有“AS”时执行查询。

这不起作用并导致代码为 500 的服务器错误

SELECT shade,pattern,
CASE 
WHEN colour = 'red' THEN 'hot'
WHEN colour = 'orange' THEN 'hot'
WHEN colour = 'blue' THEN 'cold'
WHEN colour = 'white' THEN 'cold'
ELSE 'UKNOWN'
END AS colourTemp,
texture
FROM tableTest

这行得通

SELECT shade,pattern,
CASE 
WHEN colour = 'red' THEN 'hot'
WHEN colour = 'orange' THEN 'hot'
WHEN colour = 'blue' THEN 'cold'
WHEN colour = 'white' THEN 'cold'
ELSE 'UKNOWN'
END,
texture
FROM tableTest

我当前的服务器有 mysql 5.6。我尝试了在带有 mariaDb 的服务器上不起作用的查询，但它没有错误

Answer 1

试试这个(大小写值不应该是表达式的一部分):

SELECT shade,pattern,
  CASE colour
    WHEN 'red' THEN 'hot'
    WHEN 'orange' THEN 'hot'
    WHEN 'blue' THEN 'cold'
    WHEN 'white' THEN 'cold'
    ELSE 'UKNOWN'
  END AS colourTemp,
texture
FROM tableTest;