mysql - 从sql中的连接中选择包含最大值的多行

Question

我在这里阅读了多篇关于类似问题的其他帖子，但似乎都没有回答我的问题。

我有一个表 works_on，其中包含员工从事的项目的所有时间:

     id     pno  hours

 123456789   1   32.50
 123456789   2    7.50
 333445555   2   10.00
 333445555   3   10.00 
 333445555  10   10.00
 ...

还有一个表project，给出works_on表中每个对应的项目编号(pno)的名称:

   pname           pno  location 

 ProductX           1   Bellaire 
 ProductY           2   Sugarland
 ProductZ           3   Houston
 Computerization   10   Stafford
...

我试图在不使用 SELECT TOP 或 LIMIT 约束的情况下获取具有最高总小时数的项目的名称，因为我们不知道有多少“最大”值。

我可以这样汇总时间:

SELECT p.pname, sub.hours FROM company.project p
JOIN
    (SELECT w.pno, SUM(IFNULL(w.hours, 0)) AS hours FROM company.works_on w
     GROUP BY w.pno) AS sub
ON p.pnumber = sub.pno;

返回结果:

   pname           hours      

 ProductX          52.50
 ProductY          37.50
 ProductZ          50.00
 Computerization   55.00
 Reorganization    25.00
 Newbenefits       55.00

即 computerization 和 newbenefits 应该理想地返回，因为它们具有最高的总小时数，但我似乎无法执行给我这个的 MAX 查询结果。

是否有解决方法可以在不使用 TOP 或 SORT/LIMIT 约束的情况下选择最大结果？

Answer 1

如果没有分析函数的帮助，这在 MySQL 中很难做到。这是一种方法:

SELECT p.pname, sub.hours
FROM company.project p
INNER JOIN
(
    SELECT pno, SUM(hours) AS hours
    FROM company.works_on
    GROUP BY pno
) sub
    ON p.pnumber = sub.pno
WHERE
    sub.hours = (SELECT SUM(hours) FROM company.works_on
                 GROUP BY pno ORDER BY SUM(hours) DESC LIMIT 1);

如果您使用的是 MySQL 8+ 或更高版本，那么我们可以利用 RANK():

SELECT pname, hours
FROM
(
    SELECT p.pname, sub.hours,
        RANK() OVER (PARTITION BY p.pname ORDER BY sub.hours DESC) rnk
    FROM company.project p
    INNER JOIN
    (
        SELECT pno, SUM(hours) AS hours
        FROM company.works_on
        GROUP BY pno
    ) sub
) t
WHERE rnk = 1;