查找EId和EName

Question

这是一个模式-

CREATE TABLE EMp(eId integer PRIMARY KEY, Name nvarchar(max), age integer);

CREATE TABLE Project(pId integer PRIMARY KEY, pName nvarchar(max), ploc nvarchar(max));

CREATE TABLE EmpProject(eid integer, FOREIGN KEY (eid) REFERENCES EMp(eid), pid integer FOREIGN KEY (pid) REFERENCES Project(pId), hrs integer);

我需要找到在孟买工作的至少3个项目的雇员的姓名和ID。

我试过很多选择，最后一个是-

select eid, Name from EMp E, Project P, EmpProject EP where  
EP.pNum = P.pId and P.ploc <> 'Mumbai' 
and E.eId in (select eno from EmpProject group by eno having count(*) >=3 )

提前谢谢你。

Answer 1

加入表，按雇员分组，并在HAVING子句中设置条件：

select E.eid, E.Name 
from EMp E 
inner join EmpProject EP on EP.eid = E.eid
inner join Project P  on P.pid = EP.pid
group by E.eid, E.Name
having count(distinct P.pid) >= 3 and sum(case when P.ploc = 'Mumbai' then 1 else 0 end) = 0