从有条件的2个表中选择值

Question

我想从表雇员和项目的员工从分配给多个项目的员工的项目名称中选择“值”雇员ID，员工名称， 。

谢谢大家，我可以解决他们，对这个问题感到抱歉不清楚

Answer 1

您必须使用加入，

select EmplyoeeID, FirstName, ProjectName 
from EmployeeDetail 
join ProjectDetail on EmplyoeeDetailID = EmployeeID
where (select count(*) from ProjectDetail where EmplyoeeDetailID = EmployeeID) > 1

请在此处阅读有关加入的更多信息： https：// https：//www.w3schools.com// sql/sql_join.asp

Answer 2

请尝试此（简单的内部加入）

SELECT e.EmployeeID, e.firstName, p.ProjectName
FROM Employee e
JOIN ProjectDetail p
ON e.EmployeeID = p.EmployeeDetailID

如果您只想要超过1个项目的员工，

SELECT e.EmployeeID, e.firstName, p.ProjectName
FROM Employee e
JOIN ProjectDetail p
ON e.EmployeeID = p.EmployeeDetailID
WHERE 
    (SELECT COUNT(1) FROM ProjectDetail p2
    WHERE p2.EmployeeDetailID = p.EmployeeDetailID) > 1

Answer 3

窗口功能可能是最有效的

SELECT
  e.EmployeeID,
  e.firstName,
  p.ProjectName
FROM Employee e
JOIN (
    SELECT p.*,
      count = COUNT(*) OVER (PARTITION BY p.EmployeeDetailID)
    FROM ProjectDetail p
) p ON e.EmployeeID = p.EmployeeDetailID AND p.count > 1;