mysql - 员工在每个项目上花费的最大时间

Question

我有这个查询

SELECT t.pname,MAX(t.name) ,MAX(t.total)
FROM 

(
SELECT  p.`id`,e.`name`,p.`pname`,(m.`hour`) AS total 
FROM employee e INNER JOIN epmap m ON m.`employeeID`=e.`id` INNER JOIN project p ON p.`id`=m.`projectID`

)t
GROUP BY t.id

它给出了正确的答案，但它不是一个好的方法，因为 Max(t.name) 不合适

Answer 1

这将为您提供所需的结果:

SELECT t.pname, t.name, t.hour as total
FROM (
  SELECT  p.id, e.name, p.pname, m.total,
  ROW_NUMBER() OVER(partition by p.id order by m.hour desc) rn
  FROM employee e 
  INNER JOIN epmap m ON m.employeeID=e.id 
  INNER JOIN project p ON p.id=m.projectID
) t
where t.rn = 1