sql数据库的操作，查找被删除数据和指定数据集

[快乐起航2020]

在"评论表Reply（reID,reContent)"中，reID是自增列，但Reply表中的数据被删除过，即reID不是连续的。

1）：请用一条SQL语句找出排在倒数100~120位置的那20条数据，按reID降序排列


2）：假设Reply中只被删除过1条记录，请找出被删除的记录的reID

[Tiger_Zhao]

;WITH a AS (
    SELECT TOP 120 *,
           ROW_NUMBER() OVER(ORDER BY reID DESC) rn
      FROM Reply
  ORDER BY reID DESC
)
    SELECT reID,
           reContent
      FROM a
     WHERE rn BETWEEN 101 AND 120
  ORDER BY rn

;WITH a AS (
    SELECT TOP 121 *, -- 多取一条，用来判定倒数120条之前缺号
           ROW_NUMBER() OVER(ORDER BY reID)-1 rn
      FROM Reply
  ORDER BY reID DESC
)
    SELECT a1.reID - 1 missing_reID
           a0.reID, a1.reID -- 用来确认是否只缺一个号
      FROM a a0
      JOIN a a1
        ON a0.rn + 1 = a1.rn
     WHERE a1.rn BETWEEN 1 AND 20
       AND a0.reID + 1 <> a1.reID

[中国风]

既然删除的记录又需要找出来就别删除，用一个栏位标识为删除就行了

[中国风]

这样结果？

DECLARE @StartID INT=100,@End INT=120,@MaxID INT

SELECT TOP 1 @MaxID=reID FROM Reply ORDER BY reID DESC

;WITH T1
AS
(
SELECT TOP (@End-@StartID) @MaxID-ROW_NUMBER()OVER(ORDER BY @StartID)+1 AS ID,ROW_NUMBER()OVER(ORDER BY @StartID) AS Ord FROM sys.columns AS a ,sys.columns AS b
)
SELECT  a.ID
		,CASE WHEN b.reID  IS NULL THEN 1 ELSE 0 END AS IsDel--是否删除
FROM    T1 AS a
        LEFT JOIN Reply AS b ON a.ID = b.reID
WHERE   a.ID > 0
        AND a.Ord BETWEEN @StartID AND @End

[zhll2046]

DECLARE @T TABLE(reID INT,COL1 VARCHAR(10));

INSERT INTO @T 
VALUES
(1,'something'),
(2,'something'), --倒数第二
(4,'something'), --倒数第三
(5,'something'),
(6,'something');

SELECT * FROM @T

--请用一条SQL语句找出排在倒数2-3位置的那2条数据，按reID降序排列

SELECT * FROM @T ORDER BY reID DESC
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY

--假设Reply中只被删除过1条记录，请找出被删除的记录的reID
SELECT reID+1 missingReID FROM @T T WHERE NOT EXISTS(SELECT 1 FROM @T WHERE reID=t.reID+1) 
						AND reID<>(SELECT MAX(reID) FROM @T)

[快乐起航2020]

太高深了，O(∩_∩)O谢谢大家，散分