sql - 如何加入四个表，具有多个约束

Question

我必须编写一条 SQL 语句来显示类似类型的总和，我还需要从另一个表中添加另一个排除约束。

这是使用 Microsoft Access 2010。问题如下：编写一条 SQL 语句以显示每种 OWNER 类型的 HoursWorked 总和，但排除 ExperienceLevel 为 Junior 的员工的服务，并排除任何少于三个成员的 Type。

 SELECT OWNER.Type, SUM(HoursWorked) AS Total_Work_Hours
FROM OWNER AS O, PROPERTY AS P, SERVICE AS S, EMPLOYEE AS E
WHERE O.OwnerID = P.OwnerID
AND P.Property= S.PropertyID
AND S.EmployeeID=E.EmployeeID
AND O.Type>3;

标签： sqlms-accessinner-join

Answer 1

你可以试试这个。

SELECT O.[Type], SUM(e.HoursWorked) AS Total_Work_Hours,count(e.employeeid) as Total_Members
FROM OWNER AS O, PROPERTY AS P, SERVICE AS S, EMPLOYEE AS E
WHERE O.OwnerID = P.OwnerID
AND P.Property= S.PropertyID
AND S.EmployeeID=E.EmployeeID
AND e.ExperienceLevel NOT IN ('Junior')
Group by o.[Type]
Having count(e.employeeid)>=3;