mysql - ORA-00905 : missing keyword for THEN clause of CASE?

Question

我有以下查询:

SELECT name,tid FROM table_name WHERE status=1 AND
CASE
WHEN EXISTS (SELECT 1 FROM table_name WHERE name='name1' AND tid='tid1' AND status=1)
THEN name='name1' AND tid='tid1'
ELSE name='name2' AND tid='tid2'
END;

上面的查询适用于 postgres 和 mysql，但在 Oracle 中我收到此错误:

THEN name='name1' AND AND tid='tid1'
         *
ERROR at line 4:
ORA-00905: missing keyword

有人可以告诉我这里出了什么问题以及如何更正查询以适用于所有三个 - postgres 、 mysql 和 oracle

Answer 1

在 Oracle 中，CASE 语句不能返回 bool 表达式。这是一种无需 CASE 即可完成所需操作的方法:

SELECT name, tid
FROM table_name
WHERE status = 1 AND
      (EXISTS (SELECT 1 FROM table_name WHERE name = 'name1' AND tid = 'tid1' AND status = 1) AND
       name = 'name1' AND tid = 'tid1'
      ) OR 
      (NOT EXISTS (SELECT 1 FROM table_name WHERE name = 'name1' AND tid = 'tid1' AND status = 1) AND
       name = 'name2' AND tid = 'tid2'
      );

或者，您可以将其表达为:

SELECT name, tid
FROM table_name
WHERE status = 1 AND
      (CASE WHEN EXISTS (SELECT 1 FROM table_name WHERE name = 'name1' AND tid = 'tid1' AND status = 1) 
            THEN (CASE WHEN name = 'name1' AND tid = 'tid1' THEN 'true' END)
            ELSE (CASE WHEN name = 'name2' AND tid = 'tid2' THEN 'true' END)
      ) = 'true'

用 bool 值理解CASE可能很棘手。对于嵌套的 CASE 语句来说更是如此。然而，这个版本确实具有只运行一次子查询的优点。