Casewhenthen，但在when和then之前有AND条件

Question

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

例如当 '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 。
此处的文档

Answer 2

任何计算结果为布尔值（true 或 false）的内容都可以进入 CASE 语句的 WHEN 条件。因此，您可以将 'r' 替换为：

('r' AND table1.name='jones')

再考虑一下，您可能不得不丢失 CASE 之后的 table1.event

SELECT table1.id, table1.name,
    CASE 
        WHEN (table1.event = 'r' AND table1.name='Jones') THEN 'very high'
        WHEN table1.event = '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 3

从 case切换当<值>时then ... 到 case when然后...：

CASE 
WHEN table1.event = 'r' AND table1.active = 1 THEN 'very high'
...