当时的情况，但在此之前和之后具有AND条件

Question

在下面的查询中，我想在CASE的WHEN内以及在THEN之前添加AND条件，这是可能的吗？

例如WHEN’r’AND table1.name =“ jones”然后’非常高’

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

请注意，您需要table1.event在CASE声明后删除。
这里的文件