使用IN子句的子查询

本文介绍了使用IN子句的子查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

----------
User
----------
user_ID(pk)
UserEmail

----------
Employer1
----------
Emp1ID(pk)
Emp1NO

----------
Employer2
----------
Emp2ID(pk)
Emp2NO

----------
Project
----------
ProjEmpID
ProjEmpMGRID

我需要显示用户电子邮件ID.表之间的关系如下所示:在Employer(1& 2)表中，EmpID包含User表中的UserID值.

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.

雇主编号"与项目"表中的值相关.EmpNo包含ProjEmpID，ProjEmpMGRID中的值.

the Employer No relates to the values from the Project table.EmpNo contains values from 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)

但是我在子查询中收到错误，指出IN子句中的参数太多.还有其他方法可以重写查询以获取结果.任何帮助，将不胜感激.

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.

推荐答案

您只能从子查询中返回一列.如果您只想同时使用员工ID和经理ID，则可以将联合查询作为您的子查询:

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:

    emp.Emp2NO IN
(select ProjEmpID from project
 union
 select ProjEmpMGRID from project)

或重写以使用两个IN查询，每个查询具有单独的子查询.

or rewriting to use two IN queries with separate subqueries for each.

这篇关于使用IN子句的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！