如何计算各薪资与最低薪资的差额

Question

我正在使用sql server2008 rs1，但我在查询时遇到了问题。我的表名为: employees，表列为：first name , last name , job title , gender , date of birth and salary。

必填项:显示职称、员工姓名、员工薪资与该职位最低薪资的差额

我试着这样解决它：

SELECT First_Name, Title ,Salary-min(Salary) AS (Differance) FROM Employees;

但是这引起了一个错误，请帮我提前谢谢

Answer 1

假设最低工资是所有员工的最低工资，您需要从所有员工中选择最低工资并使用；

SELECT 
  First_Name, 
  Title, 
  Salary - (SELECT MIN(SALARY) FROM Employees) As Difference 
FROM Employees

Answer 2

尝尝这个。

;WITH cte
     AS (SELECT Min(Salary) Differance
         FROM   Employees)
SELECT First_Name,
       Title,
       Salary - (SELECT * FROM   cte) AS Differance
FROM   Employees; 

Answer 3

可以使用子查询来计算最低工资：

SELECT First_Name, Title ,Salary-(SELECT MIN(e2.Salary)
                                  FROM Employees e2) AS Difference 
FROM Employees

您不需要担心这是为每一行计算的。