在某些版本的 Oracle 中，相关子查询是否有嵌套限制?
以下代码可以帮助您理解我的问题:
create table con ( content_id number);创建桌垫(material_id 号、content_id 号、分辨率号、file_location varchar2(50))；创建表 con_groups (content_group_id number, content_id number);插入 con 值 (99);插入 mat 值 (1, 99, 7, 'C:foo.jpg');插入 mat 值 (2, 99, 2, '\serverxyz.mov');插入 mat 值 (3, 99, 5, '\server2xyz.wav');插入 con 值(100)；插入 mat 值 (4, 100, 5, 'C:ar.png');插入 mat 值 (5, 100, 3, '\serverxyz.mov');插入 mat 值 (6, 100, 7, '\server2xyz.wav');插入 con_groups 值 (10, 99);插入 con_groups 值 (10, 100);犯罪;选择 m.material_id,(选择文件位置从(选择文件位置从垫子哪里 mat.content_id = m.content_idORDER BY 分辨率 DESC) special_mats_for_this_contentWHERE rownum = 1) special_mat_file_location从垫米WHERE m.material_id IN(选择 material_id从垫子con.content_id = mat.content_id 上的内部连接 concon_groups.content_id = con.content_id 上的内部连接 con_groups其中 con_groups.content_group_id = 10);请将查询末尾的数字 10 视为参数.换句话说，这个值在这个例子中只是硬编码的；它会根据输入而改变.
我的问题是:为什么我会收到错误
"M"."CONTENT_ID": 无效标识符对于嵌套的相关子查询?是否有某种嵌套限制?这个子查询需要对结果集中的每一行运行，因为结果会根据 content_id 改变，每行可能不同.我如何使用 Oracle 完成此任务?
并不是说我要开始 SQL Server 与 Oracle 的讨论，而是我来自 SQL Server 背景，我想指出以下等效查询在 SQL Server 上运行良好:
create table con ( content_id int);创建桌垫( material_id int, content_id int, resolution int, file_location varchar(50))；创建表 con_groups (content_group_id int, content_id int);插入 con 值 (99);插入 mat 值 (1, 99, 7, 'C:foo.jpg');插入 mat 值 (2, 99, 2, '\serverxyz.mov');插入 mat 值 (3, 99, 5, '\server2xyz.wav');插入 con 值(100)；插入 mat 值 (4, 100, 5, 'C:ar.png');插入 mat 值 (5, 100, 3, '\serverxyz.mov');插入 mat 值 (6, 100, 7, '\server2xyz.wav');插入 con_groups 值 (10, 99);插入 con_groups 值 (10, 100);选择 m.material_id,(选择文件位置FROM (SELECT TOP 1 file_location从垫子哪里 mat.content_id = m.content_idORDER BY 分辨率 DESC) special_mats_for_this_content) special_mat_file_location从垫米WHERE m.material_id IN(选择 material_id从垫子con.content_id = mat.content_id 上的内部连接 concon_groups.content_id = con.content_id 上的内部连接 con_groups其中 con_groups.content_group_id = 10);能否请您帮我理解为什么我可以在 SQL Server 中执行此操作，而不能在 Oracle 9i 中执行此操作?如果存在嵌套限制，我如何在 Oracle 中的单个选择查询中完成此操作，而无需求助于循环和/或临时表?
解决方案Oracle 的最新版本没有限制，但大多数旧版本的 Oracle 有 1 级深度的嵌套限制.
这适用于所有版本:
SELECT (选择 *FROM 双 dnWHERE dn.dummy = do.dummy)FROM 双做此查询适用于 12c 和 18c，但不适用于 10g 和 11g.(但是，至少有一个 10g 版本允许此查询.并且有一个补丁可以在 11g 中启用此行为.)
SELECT (选择 *从 (选择 *FROM 双 dnWHERE dn.dummy = do.dummy)哪里 rownum = 1)FROM 双做如有必要，您可以使用窗口函数来解决此限制(您也可以在 SQL Server 中使用:)
SELECT *从 (SELECT m.material_id, ROW_NUMBER() OVER (PARTITION BY content_id ORDER BY resolution DESC) AS rn从垫米WHERE m.material_id IN(选择 con.content_idFROM con_groups加入控制ON con.content_id = con_groups.content_id哪里 con_groups.content_group_id = 10))哪里 rn = 1Here 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, '\serverxyz.mov');
insert into mat values (3, 99, 5, '\server2xyz.wav');
insert into con values (100);
insert into mat values (4, 100, 5, 'C:ar.png');
insert into mat values (5, 100, 3, '\serverxyz.mov');
insert into mat values (6, 100, 7, '\server2xyz.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.
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?
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:
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, '\serverxyz.mov');
insert into mat values (3, 99, 5, '\server2xyz.wav');
insert into con values (100);
insert into mat values (4, 100, 5, 'C:ar.png');
insert into mat values (5, 100, 3, '\serverxyz.mov');
insert into mat values (6, 100, 7, '\server2xyz.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?
解决方案Recent versions of Oracle do not have a limit but most older versions of Oracle have a nesting limit of 1 level deep.
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.)
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 Server too:)
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
相关文章