无法弄清楚如何连接表

SELECT e.ManagerID, count(*) as NumberOfDepartments From HumanResources.Employee e, Person.Contact c where e.ContactID = c.ContactID group by e.ManagerID;

SELECT e.ManagerID,count(*)as NumberOfDepartments From HumanResources.Employee e,Person.Contact c其中e.ContactID = c.ContactID group by e.ManagerID;

The Goal is to write a report to display the managerid, firstname and lastname of that manager and the number of unique different departments they supervise. and only show the manager that supervises the most departments. I have to ensure that all employees are currently employed (ie enddate does not contain a date). The code above is working in showing number the managerID and number of department he runs but whenever I try to put in the first name and last name I have to put them also in the 'group by' clause and that way it makes the whole report going crazy. Please Help.
Database Here

目标是编写一份报告,以显示该经理的managerid,名字和姓氏以及他们监督的独特不同部门的数量。并且只显示监督大多数部门的经理。我必须确保所有员工目前都在工作(即enddate不包含日期)。上面的代码正在显示他运行的managerID和部门的编号,但每当我尝试输入名字和姓氏时,我也必须将它们放在'group by'子句中,这样它就会生成整个报告快要疯了。请帮忙。数据库在这里

2 个解决方案

#1

From your schema, seems that the managerID column in Employee is populated with the ID of the manager for that employee. That would explain why when adding firstName and lastName the report goes crazy, because you'd be grouping by the employee's name, not the manager's.

在您的架构中,似乎Employee中的managerID列填充了该员工的经理ID。这可以解释为什么在添加firstName和lastName时报告会变得疯狂,因为你要按员工的名字进行分组,而不是经理的名字。

Without seeing the tables content it's hard to tell, but you may have that managers can be recognised by not having managerID populated.

在没有看到表格内容的情况下很难说,但是您可能会因为没有填充managerID而可以识别管理员。

If this is the case, you can write your query like this

如果是这种情况,您可以像这样编写查询

select  e.EmployeeID, c.firstName, e.lastName, count(distinct edh.DepartmentID)
from    Employee e
join    Contact c
on      e.ContactID = c.ContactID
join    Employee e2
on      e1.EmployeeID = e2.ManagerID
join    EmployeeDepartmentHistory edh
on      e2.EmployeeID = edh.EmployeeID 
where   e.ManagerID is null and edh.EndDate is null
group by e.EmployeeID, c.firstName, e.lastName

The first instance of Employee table is the managers (because you set where e.ManagerID is null), the join with Contact gets you the managers' names, the second instance of Employee gets you all the people managed by each manager, and the join with EmployeeDepartmentHistory gets you their department (which you count on) and their EndDate, that has to be null to ensure you that they're currenty employed.

Employee表的第一个实例是管理器(因为您设置了e.ManagerID为null的位置),使用Contact的连接可以获得管理器的名称,Employee的第二个实例可以获取每个管理器管理的所有人员以及连接使用EmployeeDepartmentHistory可以获得他们的部门(您指望的部分)和他们的EndDate,必须为null才能确保他们当前就业。

Edit

Please note the way I wrote the joins; writing them as comma separated tables names in your from clause with the join condition in your where is a bad habit that should be kicked, because it makes reading, maintaining and changing them to outer joins much harder. That's why join was introduced in SQL language back in 1992.

请注意我写连接的方式;在你的from子句中将它们写成逗号分隔的表名,你的where中的连接条件是一个应该被踢的坏习惯,因为它使得读取,维护和更改它们更加难以加入外连接。这就是1992年以SQL语言引入join的原因。

#2

In MSSQL:

SELECT e.ManagerID, e.FirstName, e.LastName, COUNT(*) AS NumberOfDepartments FROM HumanResources.Employee e
INNER JOIN Person.Contact c ON e.ContactID=c.ContactID
GROUP BY e.ManagerID, e.FirstName, e.LastName

If you need it in MySql, change ON to WHERE pattern and INNER JOIN to JOIN

如果在MySql中需要它,请将ON更改为WHERE模式,将INNER JOIN更改为JOIN

#1