最小重合次数大于

Question

我不知道如何解决这个问题：有3个表格（项目，工程师和客户端）。我想知道已经雇佣了2个或更多不同的工程师的客户的名字。

表“project”包含客户端和工程师的id以及其他参数。有关客户名称和工程师薪水的信息分别载于“客户”和“工程师”。

select name 
from client, project 
where project.client_number IN 
    (
    select p.client_number 
    from project p inner join engineer e on (p.eng_number=e.eng_number) 
    where e.salary>50000 
    group by p.client_number 
    having count (distinct p.eng_number)>2 
    ) 

group by client.name 
; 

Answer 1

你是相当接近：

select name 
from client 
where client_number IN 
    (
    select p.client_number 
    from project p inner join engineer e on (p.eng_number=e.eng_number) 
    where e.salary>50000 
    group by p.client_number 
    having count (distinct p.eng_number)>2 
) 

Answer 2

我不知道我理解正确，但这种做法应该稍作修改工作： 与工程师的收入开始超过50000：

(select * from engineer where salary>50000) e 

加入项目表：

inner join 
(select * from projects) p -- this is overkill - just to give an option to filter on this level can be replaced by projects p 
on (p.eng_number=e.eng_number) 

现在加入客户表

inner join 
(select * from clients) c -- this is overkill - just to give an option to filter on this level. can be replaced by clients c 
on (p.client_number=e.client_number) 

现在你可以与外部包起来的

select name from (
(select * from engineer where salary>50000) e 
inner join 
(select * from projects) p 
on (p.eng_number=e.eng_number) 
inner join 
(select * from clients) c 
on (p.client_number=e.client_number) 
) epc 
group by c.client_number 
having count (distinct p.eng_number)>2