当时的情况，但在此之前和之后具有AND条件 [英] Case when then, but with AND condition inside when and before then

问题描述

在下面的查询中，我想在CASE的WHEN内以及何时可以添加AND条件之前?

In the below query I want to add an AND condition inside the CASE's WHEN and before THEN is that possible?

例如，当"r"和table1.name ="jones"然后非常高"时

for example 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

推荐答案

您可以像这样重写语句以完成所需的操作

You can rewrite your statement like this to accomplish what you want

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. 此处的文档

notice that you need to remove table1.event after the CASE statement. documentation here

这篇关于当时的情况，但在此之前和之后具有AND条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！