admin

添加CASE语句的结果

sql

我有以下Oracle
SQL查询。在给定名称和姓氏的情况下,它会以任一顺序查找所有带有一个名字和另一个名字首字母的记录。我创建了该score列,以将可能的匹配项放置在结果集的顶部。

SELECT
  surname,
  givenname,
  (CASE WHEN surname = 'Smith' THEN 2 ELSE 0)
    + (CASE WHEN givenname = 'John' THEN 1 ELSE 0)
    AS score
FROM person
WHERE (surname = 'Smith' AND givenname LIKE 'J%')
  OR  (surname LIKE 'S%' AND givenname = 'John')
  OR  (surname = 'John' AND givenname LIKE 'S%')
  OR  (surname LIKE 'J%' AND givenname = 'Smith')
ORDER BY
  score DESC,
  surname ASC,
  givenname ASC;

问题是score在的位置给出了错误+。IDE报告:

语法错误,应为:
/
*
|

以及执行报告:

SQL错误:ORA-00905:缺少关键字

我究竟做错了什么?有没有更好的方法来计算分数?


阅读 191

收藏
2021-06-07

共1个答案

admin

ENDCASE声明中缺少:

SELECT
  surname,
  givenname,
  (CASE WHEN surname = 'Smith' THEN 2 ELSE 0 END)  -- < add END
    + (CASE WHEN givenname = 'John' THEN 1 ELSE 0 END)  -- < add END
    AS score
FROM person
WHERE (surname = 'Smith' AND givenname LIKE 'J%')
  OR  (surname LIKE 'S%' AND givenname = 'John')
  OR  (surname = 'John' AND givenname LIKE 'S%')
  OR  (surname LIKE 'J%' AND givenname = 'Smith')
ORDER BY
  score DESC,
  surname ASC,
  givenname ASC;
2021-06-07