mysql - SQL:组合 sum 和 max 聚合函数

Question

如何从以下查询中获取最大值:

select sum(hours) from works_on group by pno;   
+------------+
| sum(hours) |
+------------+
|      52.50 |
|      50.00 |
|      55.00 |
|      25.00 |
|      55.00 |
+------------+

我想要的是:

|      55.00 |
|      55.00 |

提前致谢。

Answer 1

使用Having 子句和子查询。像这样

SELECT Sum(hours) 
FROM   works_on 
GROUP  BY pno 
HAVING Sum(hours) = (SELECT Sum(hours) h 
                     FROM   works_on 
                     GROUP  BY pno 
                     ORDER  BY h DESC 
                     LIMIT  1) 

但是在 SQL SERVER 中它真的很容易，我们有 TOP 1 with Ties 避免了 sub-query