以下代码可以帮助您理解我的问题：
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,2,'\\server\xyz.mov');
insert into mat values (3,5,'\\server2\xyz.wav');
insert into con values (100);
insert into mat values (4,100,'C:\bar.png');
insert into mat values (5,3,'\\server\xyz.mov');
insert into mat values (6,'\\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);
请将查询末尾的数字10视为参数.换句话说,这个值在这个例子中只是硬编码;它会根据输入而改变.
我的问题是：为什么我会收到错误
"M"."CONTENT_ID": invalid identifier
对于嵌套的相关子查询？有某种嵌套限制吗？需要为结果集中的每一行运行此子查询,因为结果将根据content_id进行更改,而content_id对于每一行可能会有所不同.如何通过Oracle实现这一目标？
不是说我正在尝试启动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,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);
你能帮我理解为什么我可以在sql Server中执行此操作但不能在Oracle 9i中执行此操作吗？如果存在嵌套限制,如何在Oracle中的单个选择查询中完成此操作而不使用循环和/或临时表？
解决方法
最新版本的Oracle没有限制,但大多数旧版本的Oracle具有1级深度的嵌套限制.
这适用于所有版本：
SELECT (
SELECT *
FROM dual dn
WHERE dn.dummy = do.dummy
)
FROM dual do
此查询在12c和18c中有效,但在10g和11g中不起作用. (但是,至少有一个10g版本允许此查询.并且有一个补丁可以在11g中启用此行为.)
SELECT (
SELECT *
FROM (
SELECT *
FROM dual dn
WHERE dn.dummy = do.dummy
)
WHERE rownum = 1
)
FROM dual do
如有必要,您可以使用窗口函数解决此限制(您也可以在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
