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

Question

我想从分配给多个项目的员工的表employee和project中选择值Employee ID, Employee first name, project name。

谢谢大家，我可以解决这些问题，抱歉这个问题不清楚

Answer 1

必须使用JOIN

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

请在这里阅读更多关于连接的信息:https://www.w3schools.com/sql/sql_join.asp

Answer 2

试试这个(一个简单的INNER JOIN)

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;