我有以下Oracle SQL查询。在给定名称和姓氏的情况下,它会以任一顺序查找所有带有一个名字和另一个名字首字母的记录。我创建了该score列,以将可能的匹配项放置在结果集的顶部。
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:缺少关键字
我究竟做错了什么?有没有更好的方法来计算分数?
您END在CASE声明中缺少:
END
CASE
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;