乘以不按预期工作的百分比

My problem is I am trying to list staff members whose salary is less than or equal to the average salary, which is increased by 50%, but also greater than or equal to average salary decreased by 50%. Been trying to work this out, I've got no errors, but for some reason, it's not displaying what I'm meant to be displaying.

我的问题是我试图列出工资低于或等于平均工资的工作人员,增加50%,但也大于或等于平均工资减少50%。一直试图解决这个问题,我没有错误,但由于某种原因,它没有显示我想要显示的内容。

SELECT staffNo, salary
FROM Staff
GROUP BY staffNo
HAVING salary <= AVG(salary * 0.5) OR salary >= AVG(salary / 0.5)
ORDER BY salary ASC;

This now prints:

现在打印:

Instead of what's required:

而不是所需要的:

If anything else is required, I'll provide it, but any help would be appreciate, thanks!

如果需要其他任何东西,我会提供,但任何帮助将不胜感激,谢谢!

Edit:

SELECT staffNo, salary
FROM Staff
GROUP BY staffNo
HAVING salary <= AVG(salary * 0.5) AND salary >= AVG(salary / 0.5)
ORDER BY salary ASC;

3 个解决方案

#1

I think one of your problems here is the math.

我认为你的一个问题是数学问题。

salary * 0.5 is half the salary, and salary / 0.5 is double the salary!

工资* 0.5是工资的一半,工资/ 0.5是工资的两倍!

So by your specification you're after HAVING salary BETWEEN AVG(salary) * 1.5 AND AVG(salary) * 0.5.

所以根据你的规范,你在获得AVG(工资)* 1.5和AVG(工资)* 0.5之间的工资之后。

I'm not 100% sure, but using AVG(salary) instead of having the operation inside the AVG() parameters would allow for the engine to optimize them to a single operation.

我不是百分百肯定,但是使用AVG(薪水)而不是在AVG()参数中进行操作将允许引擎将它们优化为单个操作。

#2

Try this to see what differences there are between the two queries

试试这个,看看两个查询之间有什么不同

SELECT staffNo, salary, 'lessthen' type
FROM Staff
GROUP BY staffNo
HAVING salary <= AVG(salary * 0.5)
ORDER BY salary ASC
union
SELECT staffNo, salary, 'morethen' type
FROM Staff
GROUP BY staffNo
HAVING salary >= AVG(salary / 0.5)
ORDER BY salary ASC;

#3

SELECT staffNo, salary
FROM Staff
GROUP BY staffNo
HAVING salary <= AVG(salary) * 1.5 OR salary >= AVG(salary) / 2.0
ORDER BY salary ASC;