SQL查询基于三个有限制的关系

Question

• 客户(客户ID、名字、姓氏、电子邮件、邮政编码(
• 推销员(SalesmanNo，empName(
• 帐户(AccountNo、SalesmanNo、CustomerID、AccountType(

如何实现这一点：

从Sean和Jean那里获得客户购买的名称，我相信通过下面的嵌套查询语句，我可以从两个销售人员那里获得购买的客户ID。

结果类似

CustomerID 
1.   043
2.   100
3.   203
4.   011
5.   045
6.   008

((select distinct A.CustomerID as CustomerID from account A
Inner Join Salesman S on A.SalesmanNo = S.SalesmanNo
where S.empname = 'Sean')
Intersect
(select distinct A.CustomerID as CustomerID from account A
Inner Join Salesman S on A.SalesmanNo = S.SalesmanNo
where S.empname = 'Jean'))

但是当我添加时

Select C.Firstname, C.LastName from Customer C
Inner Join
((select distinct A.CustomerID as CustomerID from account A
Inner Join Salesman S on A.SalesmanNo = S.SalesmanNo
where S.empname = 'Sean')
Intersect
(select distinct A.CustomerID as CustomerID from account A
Inner Join Salesman S on A.SalesmanNo = S.SalesmanNo
where S.empname = 'Jean')) 
As CID on C.CustomerID = A.CustomerID

我收到错误ORA-00905：缺少关键字00905.00000-"；缺少关键字"；*原因：
*措施：CID是标志，所以当我放置光标时，CID(语法错误。部分识别的规则(铁路图(：

---

请帮助

Answer 1

尽管JOIN和IN会得到类似的结果，但最好使用IN谓词进行过滤，并使用JOIN部分来用更多的数据丰富数据。因为从IN开始的这些年里，你只需要过滤，不需要试图记住或理解任何特定JOIN的目的。

您的错误是由表别名CID之前的AS引起的，因为Oracle不希望AS在它之前。查询是：

Select C.Firstname, C.LastName
from Customer C
where CustomerID in (
select a.CustomerID
from Account A
where a.SalesmanNo in (select s.SalesmanNo from Salesman s where S.empname in ('Sean','Jean'))
group by a.CustomerID
having count(distinct a.SalesmanNo) = 2
)

Answer 2

您可以尝试下面的-

select A.CustomerID as CustomerID 
from account A Inner Join Salesman S on A.SalesmanNo = S.SalesmanNo
where S.empname in ('Sean','Jean') group by A.CustomerID
having count(distinct S.empname)=2