Postgres 使用 CTE 优先更新表

如果您的小提琴中的最后一条出勤记录有误并被删除，那么我的方法是：

1. generate_series()使用并根据优先级分配行号将当月的休假余额扩展到行
2. 为一个月内的缺​​勤分配行号
3. 计算从缺勤到离开记录的左连接变化

with leave_rows as (
  select b.*, 
         row_number() over (partition by emp_code, yearmonth
                                order by priority) as use_order
    from emp_leave_balance b
         cross join lateral generate_series(1, b.balance, 1)
), absence_rows as (
  select a.*, 
         row_number() over (partition by emp_code, yearmonth
                                order by date) as use_order
    from attendance a
   where attendance = 0
), changes as (
  select a.emp_code, a.date, a.yearmonth, a.attendance, l.leave_type
  from absence_rows a
       left join leave_rows l
         on (l.emp_code, l.yearmonth, l.use_order) = 
               (a.emp_code, a.yearmonth, a.use_order)
)
update attendance 
   set leave = c.leave_type
  from changes c
 where (c.emp_code, c.date) = (attendance.emp_code, attendance.date)
;

你更新的小提琴