如何从employee表获取max(salary*months)及其出现次数？

我需要筛选出salary * months的最大值，以及该值在表中的出现次数。

数据样本 #

employee_id , name , months , salary
12          , Ami  , 15     , 1230
14          , Bik  , 1      , 678
15          , Tom  , 10     , 500
16          , Bob  , 12     , 3456
17          , Wil  , 1      , 9000
18          , Tim  , 14     , 1239
22          , Jil  , 15     , 1230

表结构说明 #

该表仅包含4列：employee_id, name, months, salary

已尝试的查询 #

Query #1 #

仅能获取最大值，无法统计次数：

select 
    max(msal) 
from 
    (select 
         employee_id, 
         name, 
         salary, 
         months, 
         (salary * months) as msal 
     from 
         employee);

Query #2 #

无意义的自连接，未实现目标：

select 
    e1.employee_id, 
    e1.months, 
    e1.salary, 
    e2.employee_id, 
    (e2.salary * e2.months) as earnings 
from 
    employee e1 
join 
    employee e2 on e1.employee_id = e2.employee_id;

Query #3 #

仅排序了收益，但未提取最大值及次数：

select 
  emp,earnings 
from 
  (
    select 
      e1.employee_id as emp, 
      e1.months, 
      e1.salary, 
      e2.employee_id, 
      (e2.salary * e2.months) as earnings 
    from 
      employee e1 
      join employee e2 on e1.employee_id = e2.employee_id
  ) 
order by 
  earnings desc;

手动计算结果 #

15  * 1230   ->  18450
1   * 678    ->  678
10  * 500    ->  5000
12  * 3456   ->  41472
1   * 9000   ->  9000
14  * 1239   ->  17346
15  * 1230   ->  18450

预期输出 #

41472 1

• 41472：salary * months的最大值
• 1：该最大值出现的次数

解决方案 #

方法1：子查询+分组统计 #

适用于所有主流数据库：

SELECT 
    msal AS max_earnings, 
    COUNT(*) AS occurrence_count
FROM (
    SELECT salary * months AS msal
    FROM employee
) AS earnings_subquery
WHERE msal = (SELECT MAX(salary * months) FROM employee)
GROUP BY msal;

方法2：窗口函数（适用于支持窗口函数的数据库） #

比如MySQL 8+、PostgreSQL、SQL Server等：

SELECT DISTINCT
    MAX(salary * months) OVER () AS max_earnings,
    COUNT(*) OVER (PARTITION BY salary * months) AS occurrence_count
FROM employee
WHERE salary * months = MAX(salary * months) OVER ();

说明 #

• 方法1先通过子查询计算所有员工的薪资总收益，再筛选出等于最大值的记录并统计数量
• 方法2利用窗口函数一次性计算最大值和分组计数，效率更高

内容的提问来源于stack exchange，提问作者kiric8494