你可以用 row_number() . . . 两次：

select d.*
from (select d.*,
             row_number() over (partition by id order by turn_time desc) as seqnum_turntime
      from (select d.*,
                   row_number() over (partition by id order by beginning_end desc) as seqnum_round
            from data d
           ) d
      where seqnum_round <= 2
     ) d
where seqnum_turntime = 1;

最里面的子查询获得前两轮。外部子查询获得最大值。
您也可以在不使用窗口函数的情况下表达这一点：

select top (1) with ties d.*
from data d
where d.beginning_date <= (select d2.beginning_date
                           from data d2
                           where d2.id = d.id
                           offset 1 fetch first 1 row only
                          )
order by row_number() over (partition by id order by turntime desc);

SELECT
  ID
 ,turn_time
 ,beginning_date
 ,end_date
FROM 
(
  SELECT
    ID
   ,MAX(turn_time) OVER (PARTITION BY Id ORDER BY BeginningDate ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS turn_time --Maximum turn time of the current row and preceding row
   ,MIN(BeginningDate) OVER (PARTITION BY Id ORDER BY BeginningDate ROWS BETWEEN  1 PRECEDING AND CURRENT ROW) AS beginning_date --Minimum begin date over current row and preceding row (could also use LAG)
   ,end_date
   ,ROW_NUMBER() OVER (PARTITION BY Id ORDER BY BeginningDate) AS Turn_Number
  FROM
    <whatever your table is>
) turn_summary
WHERE
  Turn_Number = 2