如何在sql中获得1到Next 1之间的最大No

小编典典

如何在sql中获得1到Next 1之间的最大No

sql

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)



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


DROP TABLE #t1

我想从 1 到 Next 1 获取最大值 / 从 1 开始到 Next 1 /

在此处输入图像描述

阅读 228

2022-07-22

共1个答案

小编典典

您可以使用以下脚本。请注意，记录必须按 ID 排序！

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

2022-07-22