SQL 中的第一个值和倒数第二个值

发布于07月25日

我有一个包含4列的表:Worker_id、Month、Basic_Salary. 我正在try 编写一个脚本，返回Worker_id、最后一次工资日期、第二次基本工资日期、最后一次工资金额(从BASIC_SALARY列).

以下是我写的 playbook :

with cte as (
  select WORKER_ID,  (select max(month) from workers_table) last_salary
    , ROW_NUMBER()over (partition by worker_id order by month) rn
  from workers_table
  group by WORKER_ID,month
)
select *
from cte 
where rn=2

以下是数据样本:

enter image description here个

我得到的数据是:

WORKER_ID   last_salary rn
2011-11-11  2022-01-04  2
2011-11-12  2022-01-04  2
2011-11-13  2022-01-04  2
2011-11-14  2022-01-04  2
2011-11-15  2022-01-04  2
2011-11-16  2022-01-04  2

LAST_SALARY列不正确，我找不到修复它的方法并获得正确的结果.

推荐答案

试着像下面这样做.拿到你的cte英镑，不要用GROUP BY英镑.从cte个有条件自结合为第一个有rn = 1个，第二个有rn = 2个.更新SELECT语句.请勾选下面的查询.

;WITH cte AS (
    SELECT WORKER_ID,
           month,
           basic_salary,
           ROW_NUMBER() OVER (PARTITION BY worker_id ORDER BY month) AS rn
    FROM WORKERS_TABLE
)
SELECT c1.WORKER_ID,
       c1.month AS LastSalaryDate,
       c1.basic_salary As Last_basic_salary,
       c2.month AS SecondLastSalaryDate 
FROM cte c1
    JOIN cte c2 ON c1.WORKER_ID = c2.WORKER_ID
WHERE c1.rn = 1 AND c2.rn = 2