mysql - 在 MYSQL 的 Case When 语句中使用四个条件

Question

我想知道是否有一种方法可以在 MYSQL 中的 case when 语句中使用至少四个条件。

CASE 
   WHEN 
       rank = 0 AND 
       visibility != 'visibile' AND 
       rank = 1 AND 
       visibility = 'visibile' 
   THEN 'Yes'
   WHEN 
       rank = 1 AND 
       visibility != 'visibile' AND 
       rank = 2 AND 
       visibility = 'visibile' 
   THEN 'Yes' 
   WHEN 
       rank = 2 AND 
       visibility != 'visibile' AND 
       rank = 3 AND 
       visibility = 'visibile' 
   THEN 'Yes' 
   ELSE null 
END 'Missing in Sequence'

当我使用超过 2 个条件时，我似乎无法让它工作。

有人有什么建议吗？

谢谢

Answer 1

您的查询永远不会合乎逻辑。因为，visibility = 'visibile' 和 visibility != 'visibile' 在每种情况下都相互矛盾。因此，我尝试通过创建嵌套的 CASE 语句来更正您的 CASE 语句。尝试一下:

CASE WHEN 
     ((SELECT visibility  FROM table WHERE rank=0)!='visibile' AND 
     (SELECT visibility  FROM table WHERE rank=1)='visibile')
 THEN 'Yes'
 WHEN
 ((SELECT visibility  FROM table WHERE rank=1)!='visibile' AND 
     (SELECT visibility  FROM table WHERE rank=2)='visibile') 
 THEN 'Yes' 
 WHEN
     ((SELECT visibility  FROM table WHERE rank=2)!='visibile' AND 
     (SELECT visibility  FROM table WHERE rank=3)='visibile')  
 THEN 'Yes' 
ELSE 'Missing in Sequence' END