SQL ORA-00907 缺少右括号问题 - 在插入查询中按顺序选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9175749/
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
ORA-00907 Missing right parenthesis issue - select with order by inside insert query
提问by Vaandu
I am trying to do insert to a table and it uses one select statement for one column. Below is the illustration of my query.
我正在尝试对表进行插入，它对一列使用一个选择语句。下面是我的查询的说明。
INSERT INTO MY_TBL (MY_COL1, MY_COL2)
VALUES (
(SELECT DATA FROM FIR_TABL WHERE ID = 1 AND ROWNUM = 1 ORDER BY CREATED_ON DESC),
1
);
It throws ORA-00907 Missing right Parenthesis. If I remove ORDER BYfrom this, it works as expected. But I need order it. How can I fix it?
它抛出ORA-00907 Missing right Parenthesis. 如果我ORDER BY从中删除，它会按预期工作。但我需要订购它。我该如何解决？
回答by Ben
Both the current answers ignore the fact that using order byand rownumin the same query is inherently dangerous. There is absolutely no guarantee that you will get the data you want. If you want the first row from an ordered query you mustuse a sub-query:
当前的两个答案都忽略了这样一个事实，即在同一查询中使用order by和rownum本质上是危险的。绝对不能保证您会获得所需的数据。如果您想要有序查询的第一行，则必须使用子查询：
insert into my_tbl ( col1, col2 )
select data, 'more data'
from ( select data
from fir_tabl
where id = 1
order by created_on desc )
where rownum = 1
;
You can also use a function like rankto order the data in the method you want, though if you had two created_ondates that were identical you would end up with 2 values with rnk = 1.
您还可以使用类似的函数rank以您想要的方法对数据进行排序，但如果您有两个created_on相同的日期，您最终会得到 2 个带有rnk = 1.
insert into my_tbl ( col1, col2 )
select data, 'more data'
from ( select data
, rank() over ( order by created_on desc ) as rnk
from fir_tabl
where id = 1)
where rnk = 1
;
回答by John Doyle
You don't use a SELECTwhen using the VALUESkeyword. Use this instead:
SELECT使用VALUES关键字时不要使用 a 。改用这个：
INSERT INTO MY_TBL (MY_COL)
SELECT DATA FROM FIR_TABL WHERE ID = 1 ORDER BY CREATED_ON DESC
;
Your edited query would look like:
您编辑的查询如下所示：
INSERT INTO MY_TBL (MY_COL1, MY_COL2)
SELECT DATA, 1 FROM FIR_TABL WHERE ID = 1 AND ROWNUM = 1 ORDER BY CREATED_ON DESC
;
回答by Marco Predicatori
I agree that ordering should be performed when extracting data, not when inserting it.
我同意应该在提取数据时执行排序，而不是在插入数据时执行排序。
However, as a workaround, you could isolate the ORDER BY clause from the INSERT incapsulating your whole SELECT into another SELECT.
但是，作为一种解决方法，您可以将 ORDER BY 子句与将整个 SELECT 封装到另一个 SELECT 中的 INSERT 隔离。
This will avoid the error:
这将避免错误：
INSERT INTO MY_TABLE (
SELECT * FROM (
SELECT columns
FROM table
ORDER BY clause
)
)
