请检查下一个查询:

WITH cte_previous_rows AS (
    SELECT Date, Staff_Id, LAG(FieldX) OVER (partition by Staff_Id ORDER by [date]) as Prev_Row
FROM Sales 
) UPDATE Sales
SET FieldX = (CASE 
    WHEN Staff_id_sales < 1500 AND ClosedSale = 0 THEN 0
    WHEN Staff_id_sales = 1500 and ClosedSale = 0 THEN 5
    WHEN Staff_id_sales > 3000 and (c.Prev_Row = 1 OR c.Prev_Row = 0) THEN 2
    WHEN Staff_id_sales > 3000 and (c.Prev_Row = 2 or c.Prev_Row = 3) THEN 3 
    ELSE FieldX
END) 
FROM Sales
JOIN cte_previous_rows as c ON Sales.staff_id = c.staff_id AND Sales.Date = c.Date;

结果:

+============+==========+================+============+========+
| Date       | Staff_Id | Staff_id_sales | ClosedSale | FieldX |
+============+==========+================+============+========+
| 2000-01-01 | 1        | 500            | 0          | 0      |
+------------+----------+----------------+------------+--------+
| 2001-01-01 | 2        | 200            | 0          | 0      |
+------------+----------+----------------+------------+--------+
| 2001-02-26 | 3        | 500            | 0          | 0      |
+------------+----------+----------------+------------+--------+
| 2001-01-25 | 4        | 1500           | 0          | 5      |
+------------+----------+----------------+------------+--------+
| 2001-03-25 | 4        | 1500           | 0          | 5      |
+------------+----------+----------------+------------+--------+
| 2001-03-25 | 5        | 2500           | 0          | 1      |
+------------+----------+----------------+------------+--------+
| 2001-04-25 | 5        | 3000           | 0          | 1      |
+------------+----------+----------------+------------+--------+
| 2001-05-25 | 5        | 3500           | 0          | 2      |
+------------+----------+----------------+------------+--------+
| 2001-05-26 | 5        | 4000           | 0          | 3      |
+------------+----------+----------------+------------+--------+
| 2001-05-27 | 5        | 5000           | 0          | 3      |
+------------+----------+----------------+------------+--------+
| 2001-05-28 | 5        | 7500           | 0          | 3      |
+------------+----------+----------------+------------+--------+