oracle 使用 IN 子句的子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3144912/
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
subquery using IN clause
提问by jero
----------
User
----------
user_ID(pk)
UserEmail
----------
Employer1
----------
Emp1ID(pk)
Emp1NO
----------
Employer2
----------
Emp2ID(pk)
Emp2NO
----------
Project
----------
ProjEmpID
ProjEmpMGRID
I need to display User Email ID. The relation between the table goes like this: In the Employer(1&2) table EmpID contains the value of UserID in the User table.
我需要显示用户电子邮件 ID。表之间的关系是这样的： 在 Employer(1&2) 表中，EmpID 包含 User 表中 UserID 的值。
the Employer No relates to the values from the Project table. EmpNo contains values from ProjEmpID, ProjEmpMGRID.
雇主编号与项目表中的值相关。EmpNo 包含来自 ProjEmpID、ProjEmpMGRID 的值。
select u.user_email from users u, Employer emp
where emp.Emp1ID = u.user_id and
emp.Emp1NO IN
(select ProjEmpID,ProjEmpMGRID from project)
union
select u.user_email from users u, Employer emp
where emp.Emp2ID = u.user_id and
emp.Emp2NO IN
(select ProjEmpID,ProjEmpMGRID from project)
but i get error in the subquery stating too many parameters on the IN clause.Is there any other way i could rewrite the query to get result. any help would be appreciated.
但是我在子查询中出现错误，说明 IN 子句上的参数太多。有没有其他方法可以重写查询以获得结果。任何帮助，将不胜感激。
回答by Eric Hauser
You can only return one column from your subquery. If you just want both the employee ID and manager ID consider a union query as your subquery:
您只能从子查询中返回一列。如果您只想要员工 ID 和经理 ID，请考虑将联合查询作为您的子查询：
emp.Emp2NO IN
(select ProjEmpID from project
union
select ProjEmpMGRID from project)
or rewriting to use two IN queries with separate subqueries for each.
或重写以使用两个 IN 查询，每个查询具有单独的子查询。
回答by OMG Ponies
Eric Hauser is correct - you can't specify two columns in SELECT contained in an IN clause to one column outside.
Eric Hauser 是正确的 - 您不能将 IN 子句中包含的 SELECT 中的两列指定为外部的一列。
That said, I re-wrote your query so you don't need to use UNIONs at all:
也就是说，我重新编写了您的查询，因此您根本不需要使用 UNION：
SELECT DISTINCT u.user_email
FROM USERS u
JOIN EMPLOYER e ON u.user_id IN (e.emp1id, e.emp2id)
JOIN PROJECT p ON e.emp1no IN (p.projempid, p.projempmgrid)
OR e.emp2no IN (p.projempid, p.projempmgrid)
I also changed the query to use ANSI-92 JOIN syntax - your original uses ANSI-89.
我还更改了查询以使用 ANSI-92 JOIN 语法 - 您原来使用的是 ANSI-89。
回答by Tyler Clendenin
I would recommend not using the IN statement. Use EXISTS instead.
我建议不要使用 IN 语句。请改用 EXISTS。
http://www.techonthenet.com/sql/exists.php
http://www.techonthenet.com/sql/exists.php
Example #1
示例#1
回答by G N Mallesh
emp.Emp2NO IN
(select ProjEmpID from project) or (select ProjEmpMGRID from project)
Hope you will understant why it should be so. u can put a mail to me at [email protected]
希望你能理解为什么会这样。你可以在 [email protected] 给我发邮件
Ok i will expalin. Emp1NO is one value chcked with every value in the list of values return by a single column values where as ur query returns a matrix of many(no. of rows) X 2
好的，我会解释。Emp1NO 是一个值，用单列值返回的值列表中的每个值进行检查，其中您的查询返回许多（行数）X 2 的矩阵
