使用Cursor按员工薪资涨幅更新薪资百分比列的实现问题

我希望按日期排序，针对每位员工的薪资涨幅更新Salary1_Percentage和Salary2_Percentage列，目标效果如下表：

尝试用游标实现，但代码无法正常运行，错误代码如下：

DECLARE @CurDBNo AS INT;
DECLARE @database_name as  Varchar (100);

SELECT @DBNo = SELECT count(*) FROM ##result db;

DECLARE Database_cursor CURSOR FOR

SELECT database_name FROM ##result db order by database_name, backup_start_date;

OPEN Database_cursor;

FETCH NEXT FROM Database_cursor INTO @CurDBNo

WHILE @@FETCH_STATUS = 0
  BEGIN  
      UPDATE ##result
      SET    Salary1_Percentage 
 = select @NewValue = isnull(FORMAT(Round((Salary1 - LAG (Salary1) OVER (ORDER BY Database_Name  ASC))/LAG (Salary1) OVER (ORDER BY Database_Name ASC)*100, 2, 1), '0.00'),0)+ ' %' AS Percents
             from ##result where EmployeeName = @EmployeeName
      WHERE  CURRENT OF employee_cursor;

      UPDATE ##result
      SET    Salary2_Percentage =Select  isnull(FORMAT(Round((Salary2 - LAG (Salary2) OVER (ORDER BY Database_Name  ASC))/LAG (Salary2) OVER (ORDER BY Database_Name ASC)*100, 2, 1), '0.00'),0)+ ' %' AS Percents
             from ##result where EmployeeName =@EmployeeName      
      
     
      WHERE  CURRENT OF employee_cursor;
     
      WHERE  CURRENT OF Database_cursor;
     
      SELECT @DBNo = @DBNo + 1;

      FETCH NEXT FROM Database_cursor INTO @CurDBNo
  END;

CLOSE Database_cursor;

DEALLOCATE Database_cursor;

原代码错误点 #

1. 变量错误：@DBNo未声明就赋值；@CurDBNo是INT类型，却用来接收字符串类型的database_name，类型不匹配。
2. 游标错误：仅声明了Database_cursor，但代码中使用了未定义的employee_cursor。
3. 语法错误：UPDATE语句的SET子句嵌套写法不符合SQL规范；重复使用WHERE CURRENT OF，语法违规。
4. 逻辑错误：窗口函数按Database_Name排序，实际应该按员工姓名分组、日期排序，才能计算每位员工的薪资涨幅。

正确实现方案 #

不需要使用游标，直接用窗口函数LAG()结合CTE（公共表表达式）即可高效完成更新，代码如下：

WITH SalaryChanges AS (
    SELECT 
        员工姓名,
        日期,
        薪资1,
        薪资2,
        -- 计算薪资1涨幅百分比，首次记录显示0%
        CASE 
            WHEN LAG(薪资1) OVER (PARTITION BY 员工姓名 ORDER BY 日期) IS NULL 
                OR LAG(薪资1) OVER (PARTITION BY 员工姓名 ORDER BY 日期) = 0 
            THEN '0 %'
            ELSE CONCAT(
                ROUND((薪资1 - LAG(薪资1) OVER (PARTITION BY 员工姓名 ORDER BY 日期)) * 100.0 / LAG(薪资1) OVER (PARTITION BY 员工姓名 ORDER BY 日期), 0), 
                ' %'
            )
        END AS New_Salary1_Percentage,
        -- 计算薪资2涨幅百分比，首次记录显示0%
        CASE 
            WHEN LAG(薪资2) OVER (PARTITION BY 员工姓名 ORDER BY 日期) IS NULL 
                OR LAG(薪资2) OVER (PARTITION BY 员工姓名 ORDER BY 日期) = 0 
            THEN '0 %'
            ELSE CONCAT(
                ROUND((薪资2 - LAG(薪资2) OVER (PARTITION BY 员工姓名 ORDER BY 日期)) * 100.0 / LAG(薪资2) OVER (PARTITION BY 员工姓名 ORDER BY 日期), 0), 
                ' %'
            )
        END AS New_Salary2_Percentage
    FROM ##result
)
UPDATE ##result
SET 
    Salary1_Percentage = sc.New_Salary1_Percentage,
    Salary2_Percentage = sc.New_Salary2_Percentage
FROM ##result r
JOIN SalaryChanges sc 
    ON r.员工姓名 = sc.员工姓名 
    AND r.日期 = sc.日期 
    AND r.薪资1 = sc.薪资1 
    AND r.薪资2 = sc.薪资2;

代码说明 #

• PARTITION BY 员工姓名：按员工分组，确保仅计算同一员工的薪资变化。
• ORDER BY 日期：按日期排序，获取每位员工的上一条薪资记录。
• LAG()函数：获取同一分组中前一行的薪资值，用于计算涨幅。
• CASE语句：处理首次记录（无历史薪资）或历史薪资为0的情况，直接显示0%。

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