具有多个子查询的 oracle 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1006976/
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
oracle query with multiple subqueries
提问by
tryng to select table rows and then use subqueries to generate single row list (item1,item2,item3)
尝试选择表行，然后使用子查询生成单行列表（item1、item2、item3）
anyway sql:
无论如何 sql：
select
username,
concat(firstname || ' ', lastname) as name,
email,
phone,
(
select
ltrim(sys_connect_by_path(res, ', '), ', ')
from (
select
count(*) over() as cnt,
row_number() over(order by ofield) as rnum,
(select name from rooms where code=roomcode) as res
from adminrooms
where roomcode=admins.code) /*admins.code - come from main query but it gives error: invalid identifier*/
where cnt=rnum start with rnum=1 connect by prior rnum=(rnum-1)
) as groups
from admins
where frozen=0 and (type <> 'root' or type is null)
problem seems to be the main query field 'code' from table 'admins' don't work inside list generation query
问题似乎是表“admins”中的主要查询字段“code”在列表生成查询中不起作用
采纳答案by jle
Based on your db diagram, the best way to do it is to use a custom string aggregationfunction and then just do a group by. This is similar to mysql group_concat.
根据您的数据库图，最好的方法是使用自定义字符串聚合函数，然后进行分组。这类似于mysql group_concat。
If you make a function called string_agg() using the above link, you can use it as below in your code:
如果您使用上面的链接创建一个名为 string_agg() 的函数，则可以在代码中如下使用它：
select a.username, string_agg(c.name) from admins a, adminrooms b, groups c
where a.code=b.admincode
and b.groupcode=c.code
group by a.username
Here is the string_agg function create script. Just run this as a script and you will have the function above (taken from the link shown above):
这是 string_agg 函数创建脚本。只需将其作为脚本运行，您将拥有上述功能（取自上面显示的链接）：
CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
g_string VARCHAR2(32767),
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER
);
/
SHOW ERRORS
CREATE OR REPLACE TYPE BODY t_string_agg IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER IS
BEGIN
sctx := t_string_agg(NULL);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER IS
BEGIN
SELF.g_string := self.g_string || ',' || value;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER IS
BEGIN
returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER IS
BEGIN
SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
RETURN ODCIConst.Success;
END;
END;
/
SHOW ERRORS
CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS
回答by Vincent Malgrat
I can reproduce your findings with a simple example. Consider:
我可以用一个简单的例子重现你的发现。考虑：
SQL> SELECT (SELECT d1.dummy FROM dual d2) d2
2 FROM dual D1;
D2
--
X
This works because the subquery "d2" can see the rows of the main query "d1" but if we add a level I get the same error as you:
这是有效的，因为子查询“d2”可以看到主查询“d1”的行，但是如果我们添加一个级别，我会得到与您相同的错误：
SQL> SELECT (SELECT NULL FROM (SELECT d1.dummy FROM dual d3))
2 FROM dual D1;
SELECT (SELECT NULL FROM (SELECT d1.dummy FROM dual d3))
FROM dual D1 ~
ORA-00904: "D1"."DUMMY": invalid identifier
Here the subquery "D3" can not see the values of the row from "D1".
这里子查询“D3”看不到“D1”行的值。
You will have to modify your query:
* join adminwith adminroomsand then use the sys_connect_by_pathor
* write a function that will take a code as parameter and will output the result of your select.
您将不得不修改您的查询：
* join adminwithadminrooms然后使用sys_connect_by_pathor
* 编写一个函数，该函数将代码作为参数并输出您的选择结果。
Provide us with CREATE TABLE and sample data if you want an example.
如果您需要示例，请向我们提供 CREATE TABLE 和示例数据。
回答by jle
Try to rewrite you subquery
尝试重写你的子查询
(
select
count(*) over() as cnt,
row_number() over(order by ofield) as rnum,
(select name from rooms where code=roomcode) as res
from adminrooms
where roomcode=admins.code)
as table in main query like
作为主查询中的表，如
... from admins,
(
select
roomcode,
count(*) over() as cnt,
row_number() over(order by ofield) as rnum,
(select name from rooms where code=roomcode) as res
from adminrooms) t
where t.roomcode = admins.code...
And use wmsys.WM_CONCAT()instead of sys_connect_by_path()
并使用wmsys.WM_CONCAT()代替sys_connect_by_path()
回答by Erich Kitzmueller
I guess that there is no column "code" in the "admins" table... it's just a little mistake. If there was, you wouldn't want to join with the roomcode of adminrooms, rather something like "admincode=admins.code".
我猜“管理员”表中没有“代码”列......这只是一个小错误。如果有的话，你不会想加入 adminrooms 的房间代码，而是像“admincode=admins.code”这样的东西。
