oracle SQL 嵌套子查询引用祖父母列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5247698/
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 Nested Subquery Referencing Grandparents Column
提问by
I have a particularly complicated query for a report. It selects several columns from a view, and it must build a column via aggregately concatenating several fields. To complicate things further, the concatenation must contain 3 fields even if there are 0 in reality (The concatenation is comma delimited, so empty fields will still be noticed).
我有一个特别复杂的报告查询。它从一个视图中选择多个列，并且它必须通过聚合连接多个字段来构建一个列。更复杂的是，即使实际上有 0 个字段，串联也必须包含 3 个字段（串联是逗号分隔的，因此仍然会注意到空字段）。
We are using Oracle 11.1.0.7.0.
我们使用的是 Oracle 11.1.0.7.0。
To provide backward compatibility (not necessary) we used the xmlagg function to perform the concatenation, I believe that has been around since Oracle 8 or 9.
为了提供向后兼容性（不是必需的），我们使用 xmlagg 函数来执行连接，我相信从 Oracle 8 或 9 开始就已经存在了。
This example will be simplified but I feel provides sufficient information. In other words, please do not focus on normalizing the table structure, this is strictly an example.
这个例子将被简化，但我觉得提供了足够的信息。换句话说，请不要专注于规范化表结构，这只是一个例子。
person_view
-----------
name
phone
address
position_id
position_table
--------------
position_id
position_title
So the query we currently have, and I admit to not being a SQL guru, is something like:
因此，我们目前拥有的查询（我承认我不是 SQL 专家）类似于：
select
name,
phone,
address,
(select
xmlagg(xmlelement(e, position_title || ',')).extract('//text()')
from
(select
position_title
from
position_table
where
position_table.position_id = person_view.position_id and
rownum <= 3
union all select '' from dual
union all select '' from dual
union all select '' from dual
)
where
rownum <= 3
)
from
person_view
My actual error is that, it seems, the subquery that ensures at least 3 rows of input cannot reference the grandparents query to determine person_view.position_id.
我的实际错误似乎是，确保至少 3 行输入的子查询无法引用祖父母查询来确定 person_view.position_id。
I get ORA-00904: "PERSON_VIEW"."POSITION_ID": invalid identifier
我得到 ORA-00904: "PERSON_VIEW"."POSITION_ID": 无效标识符
Performance is not a huge concern, as this is a report that will not be run regularly, but I need to figure out a solution to aggregate this data with an absolute 3 columns of data. Any guidance to help rewrite the query, or allow the subquery to access the relevant grandparent column is greatly appreciated.
性能不是一个大问题，因为这是一份不会定期运行的报告，但我需要找出一个解决方案，将这些数据与绝对的 3 列数据聚合。非常感谢任何有助于重写查询或允许子查询访问相关祖父列的指导。
采纳答案by Vincent Malgrat
This is a limitation in Oracle SQL: you can't reference a parent query element from a subquery more than 1 level deep.
这是 Oracle SQL 中的一个限制：不能从超过 1 级深度的子查询中引用父查询元素。
I would use a function in such a case:
在这种情况下，我会使用一个函数：
CREATE OR REPLACE FUNCTION get_title(p_position_id NUMBER) RETURN VARCHAR2 IS
l_result LONG;
l_position_num NUMBER := 0;
BEGIN
FOR cc IN (SELECT position_title
FROM position_table
WHERE position_table.position_id = p_position_id
AND rownum <= 3) LOOP
l_result := cc.position_title || ',';
l_position_num := l_position_num + 1;
END LOOP;
RETURN l_result || rpad(',', 3 - l_position_num, ',');
END;
You query would look like this:
您的查询将如下所示：
select
name,
phone,
address,
get_title(p.position_id) title
from person_view p
