多表联查获取Lev Tolstoi著作时遇ORA-00936错误求助

嘿，我一眼就揪出了问题——你的SQL语句里藏着一个超细微的语法小错误，就是这个逗号搞的鬼！

看你SELECT子句的最后部分：

select "AUTHORS"."LASTNAME" as "LASTNAME", "AUTHORS"."FIRSTNAME" as "FIRSTNAME", "BOOKS"."NAME" as "NAME", from

注意到"NAME"后面那个多余的逗号了吗？Oracle解析到这里的时候，会以为后面还得跟着一个字段或者表达式，结果紧接着是FROM关键字，直接懵了，所以抛出了"missing expression"的错误。

修正后的SQL语句 #

把那个多余的逗号删掉就万事大吉了：

select "AUTHORS"."LASTNAME" as "LASTNAME", 
       "AUTHORS"."FIRSTNAME" as "FIRSTNAME", 
       "BOOKS"."NAME" as "NAME"
from "BOOKS_AUTHORS", "BOOKS", "AUTHORS" 
where "AUTHORS"."ID"="BOOKS_AUTHORS"."ID_AUTHOR" 
  and "BOOKS_AUTHORS"."ID_BOOK"="BOOKS"."ID" 
  and "AUTHORS"."LASTNAME"="Tolstoi" 
  and "AUTHORS"."FIRSTNAME"="Lev"

额外小建议：换用显式JOIN语法更清晰 #

虽然你原来用的老式逗号连接表的方式能跑，但我更推荐用显式的JOIN语法，不仅可读性拉满，后续维护也更方便，还能避免不小心写出笛卡尔积的坑。给你改了个版本，顺便加了表别名让代码更简洁：

select a."LASTNAME" as "LASTNAME", 
       a."FIRSTNAME" as "FIRSTNAME", 
       b."NAME" as "NAME"
from "AUTHORS" a
join "BOOKS_AUTHORS" ba on a."ID" = ba."ID_AUTHOR"
join "BOOKS" b on ba."ID_BOOK" = b."ID"
where a."LASTNAME" = 'Tolstoi' 
  and a."FIRSTNAME" = 'Lev'

内容的提问来源于stack exchange，提问作者Andrei Florea