使用 OFFSET-FETCH，如何将行数默认为“所有行"? [英] Using OFFSET-FETCH, how to default number of rows to "all rows"?

问题描述

设想一个采用 @skip(偏移量)和 @take(返回的最大行数.如果 @take 是null，然后我想返回应用偏移后的所有行".

Envision a stored procedure which takes @skip (offset) and @take (maximum number of rows to return. If @take is null, then I want to return "all rows after applying the offset".

如果 @take 为 null，我可以通过计算表/视图中的行数来实现这一点，但是我必须执行两个查询，即当然，不是最优的.我希望有一个类似于 FETCH [NEXT] ALL ROWS 的选项.

I can accomplish this by counting the number of rows in the table/view if @take is null, but then I have to perform two queries which is, of course, not optimal. I was hoping there was an option similar to FETCH [NEXT] ALL ROWS.

DECLARE @skip BIGINT = 0;
DECLARE @take BIGINT = NULL;

IF (@take IS NULL) SET @take = (SELECT COUNT(*) FROM SomeTable);

SELECT *
FROM SomeTable
ORDER BY SortOrder
OFFSET @skip ROWS
FETCH NEXT @take ROWS ONLY

推荐答案

你可以使用 COALESCE:

DECLARE @skip BIGINT = 0;
DECLARE @take BIGINT = NULL;

SELECT *
FROM SomeTable
ORDER BY SortOrder
OFFSET COALESCE(@skip,0) ROWS
FETCH NEXT COALESCE(@take,0x7ffffff) ROWS ONLY

LiveDemo

0x7ffffff 与 2147483647 最大 INT 值相同.

0x7ffffff is the same as 2147483647 max INT value.

当没有提供@skip和@take时，它将从表中获取前2^31-1条记录.

When @skip and @take are not provided it will get first 2^31-1 records from table.

这篇关于使用 OFFSET-FETCH，如何将行数默认为“所有行"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！