列出百分比大于某一数字的所有员工

Question

如何列出百分比大于某个数字的所有员工，百分比是根据最高薪资计算的。

示例：

如果最大薪资为100，员工薪资为50，则百分比应显示50。

这就是我试过的：

select (salary/Max(salary)*100) as percentage from test.employeetable
where percentage > 75;

​

​

我得到的错误是：

“where”子句中未知列“百分比”

Answer 1

首先获取变量中的最大值，然后选择相关的结果。

select @max := max(salary) from test.employeetable;
select (salary/@max*100) as pc from test.employeetable having pc > 75;

注意having而不是where。

你可以显示相关的工资(等等.)也是

select @max := max(salary) from test.employeetable;
select salary,(salary/@max*100) as pc from test.employeetable having pc > 75;

不使用变量

select (salary/m.mx*100) as pc from test.employeetable, 
(select max(salary) as mx from test.employeetable) as m
having pc > 75;

Answer 2

试试这样的东西；

select * from (
select (salary / (select max(salary) from test.employeetable) * 100) as percentage from test.employeetable) Records
where percentage > 75;

Answer 3

Select (salary/Max(salary)*100) as percentage 
from test.employeetable 
where (salary/Max(salary)*100) > 75;  

在MySql中，您不能在where子句中使用"as“