mysql - 案例 when then，但在 when 和 before then 内有 AND 条件

Question

在下面的查询中，我想在 CASE 的 WHEN 和 THEN 之前添加一个 AND 条件是否可行？

例如 WHEN 'r' AND table1.name="jones"THEN 'very high'

SELECT table1.id, table1.name,
   CASE table1.event
     WHEN 'r' THEN 'very high'
     WHEN 't' THEN 'very low'
     ELSE (SELECT table2.risk FROM table2 WHERE table2.value <= table1.value
           ORDER BY table2.value DESC LIMIT 1)
   END AS risk
FROM table1
ORDER BY FIELD( table1.event, 'r', 'f', 't' ), table1.value DESC

Answer 1

你可以像这样重写你的语句来完成你想要的

SELECT table1.id, table1.name,
   CASE 
     WHEN table1.event = 'r' AND table1.name = 'jones' THEN 'very high'
     WHEN table1.event = 't' AND table1.name = 'smith' THEN 'very low'
     ELSE (SELECT table2.risk FROM table2 WHERE table2.value <= table1.value
           ORDER BY table2.value DESC LIMIT 1)
   END AS risk
FROM table1
ORDER BY FIELD( table1.event, 'r', 'f', 't' ), table1.value DESC

请注意，您需要在 CASE 语句之后删除 table1.event。 documentation here