问如何在sql中获得1到下一个1之间的最大值

EN

CREATE TABLE #t2 ( ID int, Furnace_life INT, TopLife INT NULL);

INSERT INTO #t2
(
    ID,
    Furnace_life,
    TopLife
)
SELECT t.ID,t.Furnace_life,
      LAG(t.Furnace_life) OVER (ORDER BY t.ID) TopLife
    FROM #t1 t
ORDER BY ID


SELECT * FROM #t2 AS t WHERE t.Furnace_life = 1 AND t.TopLife IS NOT NULL

DROP TABLE #t1
DROP TABLE #t2

CREATE TABLE #t1 ( ID int, Furnace_life INT);

INSERT INTO #t1(ID,Furnace_life) VALUES (1,1)
INSERT INTO #t1(ID,Furnace_life) VALUES (2,2)
INSERT INTO #t1(ID,Furnace_life) VALUES (3,3)
INSERT INTO #t1(ID,Furnace_life) VALUES (4,4) ---

INSERT INTO #t1(ID,Furnace_life) VALUES (5,1)
INSERT INTO #t1(ID,Furnace_life) VALUES (6,2)
INSERT INTO #t1(ID,Furnace_life) VALUES (7,3) ---

INSERT INTO #t1(ID,Furnace_life) VALUES (17,1)
INSERT INTO #t1(ID,Furnace_life) VALUES (18,2)


CREATE TABLE #TMPTBL (ID INT ,MAX_Furnace_life INT)

SELECT * FROM #t1 AS t ORDER BY t.ID ASC;

DECLARE @Max_Furnace_life INT = 0
DECLARE @MAXID INT = 0
DECLARE @Furnace_life INT
DECLARE @ID INT

DECLARE db_cursor CURSOR FOR 

    SELECT ID , Furnace_life FROM #t1 AS t ORDER BY t.ID ASC

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @ID ,@Furnace_life

WHILE @@FETCH_STATUS = 0  
BEGIN  
      
    IF @Furnace_life < @Max_Furnace_life
    BEGIN
        INSERT INTO #TMPTBL (ID ,MAX_Furnace_life) VALUES (@MAXID ,@Max_Furnace_life)
        SET @Max_Furnace_life = 0
        SET @MAXID = 0
    END
    ELSE
    BEGIN
        SET @Max_Furnace_life = @Furnace_life
        SET @MAXID = @ID
    END

    FETCH NEXT FROM db_cursor INTO @ID ,@Furnace_life
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

-- For End Of Records
INSERT INTO #TMPTBL(ID ,MAX_Furnace_life) SELECT TOP 1 * FROM #t1 ORDER BY ID DESC
SELECT * FROM #TMPTBL

DROP TABLE #t1
DROP TABLE #TMPTBL

WITH PrevValues AS (
    SELECT *,
      Prev = LAG(t.Furnace_life) OVER (ORDER BY t.ID)
    FROM #t1 t
),
Groups AS (
    SELECT *,
      GroupId = COUNT(CASE WHEN t.Prev = t.Furnace_life THEN NULL ELSE 1 END) OVER (ORDER BY Furnace_life ROWS UNBOUNDED PRECEDING)
    FROM PrevValues t
)
SELECT TOP (1)
  Highest_Furnace_life = MAX(g.Furnace_life)
FROM Groups g
ORDER BY
  Highest_Furnace_life DESC;