在having子句中使用别名使用子查询

Question

我有一个Mysql查询，其中

其中我有一个如下所示的表(示例数据)

Employee_id Months Salary
  1         10      200
  2         20      300
  3         30      400

现在我想找出具有最大总工资的员工数量(总工资=月*工资)

所以我的查询是这样的

子查询：

((select max(mon_sal.mc) as max_mc from (
  select months*salary as mc from employee group by employee_id) as mon_sal)
as max_mon_sal)

//求总工资的最大值

现在我的问题是，我需要找出工资最高的人数，

select max_mon_sal.max_mc,name 
from employee group by employee_id 
having salary=max_mon_sal.max_mc from (
    (select max(mon_sal.mc) as max_mc from 
      (select months*salary as mc from employee group by employee_id) as mon_sal)
    as max_mon_sal)

它显示错误。我在使用max_mon_sal alias.Please建议时遇到问题。

Answer 1

您可以简单地使用：

select count(*)
from employee
where months * salary = (
  select max(months * salary)
  from employee
);

Answer 2

SELECT COUNT(*) no_of_employees
  FROM my_table a 
  JOIN 
     ( SELECT MAX(months*salary) total FROM my_table ) b 
    ON b.total = a.months * a.salary;

Answer 3

我不确定这是否是我们想要的，即使是这种情况下更好的解决方案也是使用临时表：

SELECT Employee.employee_id,SUM(MONTHs * salary) 
FROM Employee,
   ( 
      SELECT MAX(total) value FROM (
           SELECT SUM(MONTHs * salary) as total
           FROM Employee
           GROUP BY employee_id
      ) T
   ) D
   GROUP BY Employee.employee_id,D.value
   HAVING SUM(MONTHs * salary)  = D.value