如何在SQL的新列中使用用例信息?

Question

我正在从一个在线教程系列中学习SQL，在这个视频中我们学习了如何使用用例语句。

我知道如何使用它，但后来我想到了&;好吧，我可以使用我做的列中的新数据吗?&;我试着玩，并在另一个CASE语句中添加尝试和使用UpdatedSalary列，但它不会工作。我试着用谷歌搜索了很多次，但我真的不知道如何用这个词来表达这个问题。

SELECT 
FirstName, LastName, JobTitle, Salary,
CASE 
WHEN JobTitle = 'Salesman' THEN Salary + (Salary * .10)
WHEN JobTitle = 'Accountant' THEN Salary + (Salary * .05)
WHEN Jobtitle = 'HR' THEN Salary + (Salary * .00001)
ELSE Salary + (Salary * .03)
END AS UpdatedSalary
CASE 
WHEN UpdatedSalary > 0 THEN UpdatedSalary - Salary 
ELSE 
END AS NetIncrease
FROM 
SQLTutorial.dbo.EmployeeDemographics
JOIN 
SQLTutorial.dbo.EmployeeSalary ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID

Answer 1

你不能使用这样的列别名，如果你想使用这个，你需要在两个列中放置相同的条件或需要使用外部查询。

SELECT FirstName, LastName, JobTitle, Salary,
CASE 
WHEN JobTitle = 'Salesman'
THEN Salary + (Salary * .10)
WHEN JobTitle = 'Accountant'
THEN Salary + (Salary * .05)
WHEN Jobtitle = 'HR' 
THEN Salary + (Salary * .00001)
ELSE Salary + (Salary * .03)
END
AS UpdatedSalary,
CASE 
WHEN (CASE 
WHEN JobTitle = 'Salesman'
THEN Salary + (Salary * .10)
WHEN JobTitle = 'Accountant'
THEN Salary + (Salary * .05)
WHEN Jobtitle = 'HR' 
THEN Salary + (Salary * .00001)
ELSE Salary + (Salary * .03)
END)> 0
THEN (CASE 
WHEN JobTitle = 'Salesman'
THEN Salary + (Salary * .10)
WHEN JobTitle = 'Accountant'
THEN Salary + (Salary * .05)
WHEN Jobtitle = 'HR' 
THEN Salary + (Salary * .00001)
ELSE Salary + (Salary * .03)
END)- Salary 
ELSE
END
AS NetIncrease
FROM SQLTutorial.dbo.EmployeeDemographics
JOIN SQLTutorial.dbo.EmployeeSalary
ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID

或

SELECT FirstName, LastName, JobTitle, Salary,UpdatedSalary,
CASE 
WHEN UpdatedSalary > 0
THEN UpdatedSalary - Salary 
ELSE
END
AS NetIncrease
from (
SELECT FirstName, LastName, JobTitle, Salary,
CASE 
WHEN JobTitle = 'Salesman'
THEN Salary + (Salary * .10)
WHEN JobTitle = 'Accountant'
THEN Salary + (Salary * .05)
WHEN Jobtitle = 'HR' 
THEN Salary + (Salary * .00001)
ELSE Salary + (Salary * .03)
END
AS UpdatedSalary

FROM SQLTutorial.dbo.EmployeeDemographics
JOIN SQLTutorial.dbo.EmployeeSalary
ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID)t