mysql - 如何选择在项目上工作时间最长的员工

Question

我有以下分配表:

+-----------+----------------+-------------+
| ProjectID | EmployeeNumber | HoursWorked |
+-----------+----------------+-------------+
|      1000 |              1 |       30.00 |
|      1000 |              8 |       75.00 |
|      1000 |             10 |       55.00 |
|      1100 |              4 |       40.00 |
|      1100 |              6 |       45.00 |
|      1200 |              1 |       25.00 |
|      1200 |              2 |       20.00 |
|      1200 |              4 |       45.00 |
|      1200 |              5 |       40.00 |
|      1300 |              1 |       35.00 |
|      1300 |              8 |       80.00 |
|      1300 |             10 |       50.00 |
|      1400 |              4 |       15.00 |
|      1400 |              5 |       10.00 |
|      1400 |              6 |       27.50 |
+-----------+----------------+-------------+

在下表中，我找到了超出预算的两个项目，但我必须找到在每个项目上工作时间最长的人员的员工编号:

CREATE VIEW OVER AS
    SELECT P.Department, P.ProjectID, A.EmployeeNumber, A.HoursWorked   
    FROM project AS P JOIN assignment AS A
    ON P.ProjectID = A.ProjectID
    GROUP BY P.ProjectID
    HAVING MAX(P.maxhours) < SUM(A.hoursworked);

+------------+-----------+----------------+-------------+
| Department | ProjectID | EmployeeNumber | HoursWorked |
+------------+-----------+----------------+-------------+
| Marketing  |      1000 |              1 |       30.00 |
| Marketing  |      1300 |              1 |       35.00 |
+------------+-----------+----------------+-------------+

对于这两个项目，正确的员工编号应该是 8，75 和 80 小时。

知道如何检索这些单个项目的最大工作时数吗？

Answer 1

简单的方法是这样的:

select * from (
    select ProjectID, EmployeeNumber
    from assignment
    group by 1, 2
    order by sum(HoursWorked) desc
) x
group by 1

参见 live demo在 SQLFiddle 上。

这是由于 mysql 对分组依据的特殊处理，它不允许列出所有非聚合列，在这种情况下仅返回每个组遇到的第一行。