SQL Server 环境下如何判断员工薪资是上涨还是下降

方案1：动态查询（无需修改表结构） #

直接用LAG窗口函数提取同员工上一期的薪资，通过CASE判断涨跌状态，适合不需要持久化存储状态的场景：

SELECT 
    emp_id AS 员工ID,
    salary_date AS 统计日期,
    salary AS 当期薪资,
    -- 取同员工上一次统计的薪资
    LAG(salary) OVER (PARTITION BY emp_id ORDER BY salary_date) AS 上期薪资,
    -- 判定涨跌状态
    CASE 
        WHEN LAG(salary) OVER (PARTITION BY emp_id ORDER BY salary_date) IS NULL THEN '首次统计'
        WHEN salary > LAG(salary) OVER (PARTITION BY emp_id ORDER BY salary_date) THEN '上涨'
        WHEN salary < LAG(salary) OVER (PARTITION BY emp_id ORDER BY salary_date) THEN '下跌'
        ELSE '持平'
    END AS 薪资涨跌状态
FROM 你的薪资表名

方案2：新增持久化列存储状态 #

如果需要把状态存在表中避免每次查询计算，按以下步骤操作：

步骤1：新增状态列 #

ALTER TABLE 你的薪资表名
ADD 薪资涨跌状态 NVARCHAR(10)

步骤2：批量更新历史数据的状态 #

WITH salary_compare AS (
    SELECT 
        *,
        LAG(salary) OVER (PARTITION BY emp_id ORDER BY salary_date) AS last_salary
    FROM 你的薪资表名
)
UPDATE salary_compare
SET 薪资涨跌状态 = CASE 
    WHEN last_salary IS NULL THEN '首次统计'
    WHEN salary > last_salary THEN '上涨'
    WHEN salary < last_salary THEN '下跌'
    ELSE '持平'
END

后续新增数据维护 #

后续插入新的薪资记录时，先查询对应员工最新的上期薪资，计算状态后再插入即可。

内容的提问来源于stack exchange，提问作者furkan.