oracle 左外部加入子查询?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/3603597/
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
Left Outer Join with subqueries?
提问by jero
----------
User
----------
user_ID(pk)
UserEmail
----------
Project_Account
----------
actno
actname
projno
projname
ProjEmpID
ProjEmpMGRID
Where ProjEmpID,ProjEmpMGRID is the user_id and ProjEmpMGRID can be null. I need to look up the useremail and display the table project_account. I need to query with actNo which has duplicate values.
其中 ProjEmpID,ProjEmpMGRID 是 user_id,ProjEmpMGRID 可以为空。我需要查找 useremail 并显示表 project_account。我需要使用具有重复值的 actNo 进行查询。
My query goes like this:
我的查询是这样的:
select projno,projname,actno,actname,
(select u.user_email as project_manager from project_account c left outer join users u
on u.user_id = c.ProjEmpID where actno='some no')as project_manager,
(select u.user_email as program_manager from project_account c left outer join users u
on u.user_id = c.ProjEmpMGRID where actno='someno') as program_manager
from project_account where actno='someno'
The error message I get in Oracle:
我在 Oracle 中收到的错误消息:
ora-01427 single row subquery returns more than one row
ora-01427 单行子查询返回多于一行
As my subquery returns more than one email id, I get this error. As I said, act no is not unique. I could understand the error, but I couldn't figure out the solution. I am doing a left outer join in a subquery because there might be nulls in prog manager id.
当我的子查询返回多个电子邮件 ID 时,我收到此错误。正如我所说,不采取行动不是唯一的。我可以理解错误,但我无法找出解决方案。我在子查询中执行左外连接,因为 prog manager id 中可能有空值。
Any help would be appreciated.
任何帮助,将不胜感激。
回答by Zak
The error you are getting is that one of your subqueries (either for project_manager or program_manager) is giving you back more than one ID based on your conditions. This kind of makes sense, since multiple project accounts could have the same "actno" since you haven't specified that as a Primarky Key (pk)
您遇到的错误是您的子查询之一(针对 project_manager 或 program_manager)根据您的条件返回了多个 ID。这是有道理的,因为多个项目帐户可能具有相同的“actno”,因为您尚未将其指定为 Primarky Key (pk)
furhter, rather than using subqueries, just join directly to the user tables to find the IDs
更进一步,而不是使用子查询,只需直接加入用户表即可找到 ID
select projno,projname,actno,actname,
project_user.user_email as project_manager,
program_user.user_email as program_manager
from project_account
left join User as project_user
on project_account.ProjEmpID = project_user.user_id
left join User as program_user
on project_account.ProjEmpMGRID = program_user.user_id
where actno='someno'
回答by Brett
What about something like:
怎么样:
select c.projno, c.projname, c.actno, c.actname, u.user_email as project_manager, us.user_email as program_manager
from project_account c
left outer join users u
on u.user_id = c.ProjEmpID
left outer join users us
on us.user_id = c.ProjEmpMGRID
WHERE actno = 'someno'
This way you aren't running subqueries and returning multiple results and trying to store them as one value.
这样您就不会运行子查询并返回多个结果并尝试将它们存储为一个值。
回答by sukanto
Why don't you simply use this?
你为什么不简单地使用这个?
select projno, projname, actno, actname, (select user_email from users where user_id = pa.projempid), (select user_email from users where user_id = pa.projempmgrid)
from project_account pa
select projno, projname, actno, actname, (select user_email from users where user_id = pa.projempid), (select user_email from users where user_id = pa.projempmgrid)
from project_account pa