如何在现有SQL查询结果中新增名为'PercentOfRise %'的涨幅百分比列

你可以直接在现有SELECT的字段列表中新增一个匹配涨薪规则的CASE计算逻辑，将对应岗位的涨幅比例乘以100后作为新增列即可，完整修改后的SQL如下：

SELECT 
    EmployeeDemographics.EmployeeID, 
    FirstName, 
    Lastname,
    JobTitle, 
    Salary,
    CASE 
        WHEN JobTitle = 'Data Analyst' THEN Salary + (Salary * 0.20)
        WHEN JobTitle = 'Accountant' THEN Salary + (Salary * 0.15)
        WHEN JobTitle = 'HR' THEN Salary + (Salary * 0.02)
        ELSE Salary + (Salary * 0.10)
    END AS SalaryAfterRaise,
    -- 新增涨幅百分比列
    CASE 
        WHEN JobTitle = 'Data Analyst' THEN 0.20 * 100
        WHEN JobTitle = 'Accountant' THEN 0.15 * 100
        WHEN JobTitle = 'HR' THEN 0.02 * 100
        ELSE 0.10 * 100
    END AS `PercentOfRise %`
FROM EmployeeDemographics 
INNER JOIN EmployeeSalary ON 
    EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID

注意：如果使用SQL Server数据库，可将别名的反引号替换为方括号[PercentOfRise %]；如果需要固定小数位可搭配ROUND()函数处理，例如ROUND(0.20*100,2)即可保留两位小数。

如果不想重复编写两次CASE逻辑，也可以用CTE封装涨薪比例，后续维护涨薪规则只需要修改一次即可，示例如下：

WITH emp_raise_config AS (
    SELECT 
        EmployeeDemographics.EmployeeID, 
        FirstName, 
        Lastname,
        JobTitle, 
        Salary,
        CASE 
            WHEN JobTitle = 'Data Analyst' THEN 0.20
            WHEN JobTitle = 'Accountant' THEN 0.15
            WHEN JobTitle = 'HR' THEN 0.02
            ELSE 0.10
        END AS raise_rate
    FROM EmployeeDemographics 
    INNER JOIN EmployeeSalary ON 
        EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID
)
SELECT 
    EmployeeID,
    FirstName,
    Lastname,
    JobTitle,
    Salary,
    Salary * (1 + raise_rate) AS SalaryAfterRaise,
    raise_rate * 100 AS `PercentOfRise %`
FROM emp_raise_config

内容的提问来源于stack exchange，提问作者Divesh Harchandani