时间戳

首页 > 解决方案 > 从sql中的连接中选择多行保存最大值

mysql - 从sql中的连接中选择多行保存最大值

问题描述

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

我有一个表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

即，理想情况下应该返回计算机化和新福利，因为它们具有最高的总小时数，但我似乎无法执行给出这个结果的 MAX 查询。

是否有解决方法来选择最大结果而不使用 TOP 或 SORT/LIMIT 约束？

标签： mysqlsqlmysql-workbench

解决方案

如果没有分析函数的帮助，这在 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;

推荐阅读