在某些版本的 Oracle 中，相关子查询是否有嵌套限制？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1233910/
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
Is there a nesting limit for correlated subqueries in some versions of Oracle?
提问by Nate Cook
Here is the code that will help you understand my question:
这是可以帮助您理解我的问题的代码：
create table con ( content_id number);
create table mat ( material_id number, content_id number, resolution number, file_location varchar2(50));
create table con_groups (content_group_id number, content_id number);
insert into con values (99);
insert into mat values (1, 99, 7, 'C:\foo.jpg');
insert into mat values (2, 99, 2, '\server\xyz.mov');
insert into mat values (3, 99, 5, '\server2\xyz.wav');
insert into con values (100);
insert into mat values (4, 100, 5, 'C:\bar.png');
insert into mat values (5, 100, 3, '\server\xyz.mov');
insert into mat values (6, 100, 7, '\server2\xyz.wav');
insert into con_groups values (10, 99);
insert into con_groups values (10, 100);
commit;
SELECT m.material_id,
(SELECT file_location
FROM (SELECT file_location
FROM mat
WHERE mat.content_id = m.content_id
ORDER BY resolution DESC) special_mats_for_this_content
WHERE rownum = 1) special_mat_file_location
FROM mat m
WHERE m.material_id IN (select material_id
from mat
inner join con on con.content_id = mat.content_id
inner join con_groups on con_groups.content_id = con.content_id
where con_groups.content_group_id = 10);
Please consider the number 10 at the end of the query to be a parameter. In other words this value is just hardcoded in this example; it would change depending on the input.
请将查询末尾的数字 10 视为参数。换句话说，这个值在这个例子中只是硬编码的；它会根据输入而改变。
My question is: Why do I get the error
我的问题是：为什么我收到错误
"M"."CONTENT_ID": invalid identifier
for the nested, correlated subquery? Is there some sort of nesting limit? This subquery needs to be ran for every row in the resultset because the results will change based on the content_id, which can be different for each row. How can I accomplish this with Oracle?
对于嵌套的相关子查询？是否有某种嵌套限制？这个子查询需要对结果集中的每一行运行，因为结果会根据 content_id 改变，每行可能不同。我如何使用 Oracle 完成此操作？
Not that I'm trying to start a SQL Server vs Oracle discussion, but I come from a SQL Server background and I'd like to point out that the following, equivalent query runs fine on SQL Server:
并不是我想开始 SQL Server 与 Oracle 的讨论，而是我来自 SQL Server 背景，我想指出以下等效查询在 SQL Server 上运行良好：
create table con ( content_id int);
create table mat ( material_id int, content_id int, resolution int, file_location varchar(50));
create table con_groups (content_group_id int, content_id int);
insert into con values (99);
insert into mat values (1, 99, 7, 'C:\foo.jpg');
insert into mat values (2, 99, 2, '\server\xyz.mov');
insert into mat values (3, 99, 5, '\server2\xyz.wav');
insert into con values (100);
insert into mat values (4, 100, 5, 'C:\bar.png');
insert into mat values (5, 100, 3, '\server\xyz.mov');
insert into mat values (6, 100, 7, '\server2\xyz.wav');
insert into con_groups values (10, 99);
insert into con_groups values (10, 100);
SELECT m.material_id,
(SELECT file_location
FROM (SELECT TOP 1 file_location
FROM mat
WHERE mat.content_id = m.content_id
ORDER BY resolution DESC) special_mats_for_this_content
) special_mat_file_location
FROM mat m
WHERE m.material_id IN (select material_id
from mat
inner join con on con.content_id = mat.content_id
inner join con_groups on con_groups.content_id = con.content_id
where con_groups.content_group_id = 10);
Can you please help me understand why I can do this in SQL Server but not Oracle 9i? If there is a nesting limit, how can I accomplish this in a single select query in Oracle without resorting to looping and/or temporary tables?
您能帮我理解为什么我可以在 SQL Server 中执行此操作而不是在 Oracle 9i 中执行此操作吗？如果存在嵌套限制，如何在 Oracle 中的单个选择查询中完成此操作，而无需求助于循环和/或临时表？
回答by Quassnoi
Recent versions of Oracle do not have a limit but most older versions of Oracle have a nesting limit of 1level deep.
最新版本的 Oracle 没有限制，但大多数旧版本的 Oracle 都有嵌套1深度的限制。
This works on all versions:
这适用于所有版本：
SELECT (
SELECT *
FROM dual dn
WHERE dn.dummy = do.dummy
)
FROM dual do
This query works in 12c and 18c but does not work in 10g and 11g. (However, there is at least one version of 10g that allowed this query. And there is a patch to enable this behavior in 11g.)
此查询适用于 12c 和 18c，但不适用于 10g 和 11g。（但是，至少有一个 10g 版本允许此查询。并且有一个补丁可以在 11g 中启用此行为。）
SELECT (
SELECT *
FROM (
SELECT *
FROM dual dn
WHERE dn.dummy = do.dummy
)
WHERE rownum = 1
)
FROM dual do
If necessary you can workaround this limitation with window functions (which you can use in SQL Servertoo:)
如有必要，您可以使用窗口函数来解决此限制（您也可以在其中使用SQL Server：）
SELECT *
FROM (
SELECT m.material_id, ROW_NUMBER() OVER (PARTITION BY content_id ORDER BY resolution DESC) AS rn
FROM mat m
WHERE m.material_id IN
(
SELECT con.content_id
FROM con_groups
JOIN con
ON con.content_id = con_groups.content_id
WHERE con_groups.content_group_id = 10
)
)
WHERE rn = 1
回答by Cosmin Cosmin
@QuassnoiThis was the case in oracle 9. From Oracle 10 ...
@Quassnoi在 oracle 9 中就是这种情况。从 Oracle 10 ...
From Oracle Database SQL Reference 10g Release 1 (10.1)Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levelsabove the subquery
From Oracle9i SQL Reference Release 2 (9.2)Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parentstatement.
A subquery in the WHEREclause of a SELECT statement is also called a nested subquery. You can nest up to 255 levels of subqueries in the a nested subquery.
来自Oracle 数据库 SQL 参考 10g 第 1 版 (10.1)当嵌套子查询引用表中的列时，Oracle 将执行相关子查询，该列引用父语句高于子查询任意数量的级别
从Oracle9i SQL Reference Release 2 (9.2)当子查询引用父语句中引用的表中的列时，Oracle 执行相关子查询。
SELECT 语句的WHERE子句中的子查询也称为嵌套子查询。在嵌套子查询中最多可以嵌套 255 级子查询。
I don't think it works if you have something like select * from (select * from ( select * from ( ....))))
Just select * from TableName alias where colName = (select * from SomeTable where someCol = (select * from SomeTable x where x.id = alias.col))
如果您有类似select * from (select * from ( select * from ( ....)))) 之类的东西，我认为它不起作用
只需select * from TableName alias where colName = (select * from SomeTable where someCol = ( select * from SomeTable x where x.id = alias.col))
Check out http://forums.oracle.com/forums/thread.jspa?threadID=378604
查看http://forums.oracle.com/forums/thread.jspa?threadID=378604
回答by Nate Cook
Quassnoi answered my question about nesting, and made a great call by suggesting window analytic functions. Here is the exact query that I need:
Quassnoi 回答了我关于嵌套的问题，并通过建议窗口分析函数做了一个很棒的电话。这是我需要的确切查询：
SELECT m.material_id, m.content_id,
(SELECT max(file_location) keep (dense_rank first order by resolution desc)
FROM mat
WHERE mat.content_id = m.content_id) special_mat_file_location
FROM mat m
WHERE m.material_id IN (select material_id
from mat
inner join con on con.content_id = mat.content_id
inner join con_groups on con_groups.content_id = con.content_id
where con_groups.content_group_id = 10);
Thanks!
谢谢！
