SQL: 选择中的 ORA-00933 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8817532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: ORA-00933 error in select
提问by Maxim
I'm trying to perform a somewhat complex select from 3 tables, all joined by a b_id:
我正在尝试从 3 个表中执行一个有点复杂的选择，所有表都由一个 b_id 连接：
select max(bs.b_id),
h.b_type_id,
t.name_id
from b_state as bs,
t_info as t,
history as h
where bs.b_id = t.b_id
and bs.b_id = h.b_id
and t.name_id in (???)
and bs.is_detached = ?
group by h.b_type_id,
t.name_id
In MySQL it gives exactly what I want, but it seems to fail in PSQL and Oracle, plus execution in Oracle results in: "ORA-00933: SQL command not properly ended" (which usually occurs in INSERT queries as Google suggests).
在 MySQL 中，它给出了我想要的，但在 PSQL 和 Oracle 中似乎失败了，再加上在 Oracle 中的执行结果：“ ORA-00933: SQL command not properly ended”（这通常发生在 Google 建议的 INSERT 查询中）。
I suppose there is a DB independent way to do the same. Please advice.
我想有一种独立于数据库的方法可以做到这一点。请指教。
采纳答案by aF.
Try:
尝试：
select max(bs.b_id), h.b_type_id, t.name_id
from b_state bs
inner join t_info t on bs.b_id = t.b_id
inner join history h on bs.b_id = h.b_id
where t.name_id in (???) and bs.is_detached = ?
group by h.b_type_id, t.name_id
回答by Ollie
Try this:
尝试这个：
select max(bs.b_id),
h.b_type_id,
t.name_id
from b_state bs,
t_info t,
history h
where bs.b_id = t.b_id
and bs.b_id = h.b_id
and t.name_id in (???)
and bs.is_detached = ?
group by h.b_type_id,
t.name_id
The "as" aliasing the tables was causing you a problem in Oracle.
表别名的“as”导致您在 Oracle 中出现问题。
EDIT: I have used the SQL-86 syntax as that is what you originally posted but you should really be using the SQL-92 syntax by default.
编辑：我使用了 SQL-86 语法，因为这是您最初发布的，但默认情况下您确实应该使用 SQL-92 语法。
回答by René Nyffenegger
Oracle uses :varnameto bind variables instead of ?.
Oracle 使用:varname绑定变量而不是?.
So try something like
所以尝试类似的东西
select
max(bs.b_id),
h.b_type_id,
t.name_id
from
b_state as bs,
t_info as t,
history as h
where
bs.b_id = t.b_id and
bs.b_id = h.b_id and
t.name_id in (:1, :2, :3) and
bs.is_detached = :4
group by
h.b_type_id,
t.name_id
回答by René Nyffenegger
I suggest giving an alias to the max and (if necessary) comma-separating the parameters in the inclause, like so:
我建议给 max 一个别名，并（如有必要）用逗号分隔in子句中的参数，如下所示：
select max(bs.b_id) max_b_id,
h.b_type_id,
t.name_id
from b_state as bs,
t_info as t,
history as h
where bs.b_id = t.b_id
and bs.b_id = h.b_id
and t.name_id in (?,?,?)
and bs.is_detached = ?
group by h.b_type_id,
t.name_id
