SQL server语言 - 如果每组包含50个结果，则查询第三组结果

Question

我想知道如何根据条件从特定表中获得101-150行之间的结果，如下所示:

SELECT * FROM Students
WHERE Student_Status = 'Cancelled';

可以有多个学生状态，所以我只想要取消学生的101 - 150之间的结果。

Answer 1

使用row_number窗口函数对行进行排序:

select * from
(select *, row_number() over(order by StudentID) as rn 
 from Students where Student_Status = 'Cancelled') t
where rn between 101 and 150

Answer 2

与Giorgi的答案相同，但使用CTE的方式不同;我发现它更容易阅读和工作。

;WITH t1 AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY StudentID) AS RID, *
    FROM Students
    WHERE Student_Status='Cancelled'
)
SELECT *
FROM t1
WHERE RID BETWEEN 101 AND 150

Answer 3

Offset fetch可能是你想要的:

SELECT * 
FROM Students 
WHERE Student_Status = 'Cancelled'
ORDER BY StudentId 
OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY

的例子:

declare @table table (id int)
declare @int int = 1
while @int < 200
begin
    insert into @table values (@int)
    set @int = @int+1
end
select id
from @table 
order by id 
offset 100 rows fetch next 50 rows only

Answer 4

DECLARE @RowsPerPage INT = 50; 
DECLARE @PageNumber INT = 3; 
SELECT *
FROM Students
WHERE Status = 'Cancelled' 
ORDER BY StudentID
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY

Answer 5

正如Giorgi指出的那样，OFFSET和NEXT仅在2012年可用
所以这在2008年是行不通的
让人们知道它在2012年是可用的

SELECT * FROM Students 
 WHERE Student_Status = 'Cancelled' 
 ORDER BY StudentID 
OFFSET 100 ROWS
 FETCH NEXT 50 ROWS ONLY;

,