SQL范围组的开始和结束ID [英] Sql Range Groups Start and End Id

问题描述

我有一个查询，我想分成大小为200的块，并返回每个块的开始ID和结束ID。

I have a query that I want to break into 'chunks' of size 200 and return the start id and end id of each 'chunk'.

示例：

select t.id
from t
where t.x = y --this predicate will cause the ids to not be sequential

如果示例是查询，我试图将其分为块想要返回：

If the example was the query I'm trying to break into 'chunks' I'd want to return:

（第一个ID，第200个ID），（第201个ID，第400个ID）...（最终范围ID的开始，范围ID的结束）

(1st ID, 200th ID), (201st ID, 400th ID)...(start of final range ID, end of range ID)

编辑：对于最终范围，如果它不是完整的200行，它仍应在查询中提供最终ID。

For the final range, if it is not a full 200 rows it should still supply the final id in the query.

是否有一种方法可以仅使用SQL来执行此操作，还是我不得不诉诸应用程序处理和/或类似于分页实现的多个查询？

Is there a way to do this with just SQL or will I have to resort to application processing and/or multiple queries similar to a pagination implementation?

如果有办法在SQL中执行此操作，请提供示例。

If there is a way to do this in SQL please supply an example.

推荐答案

嗯，我认为最简单的方法是使用 row_number（）：

Hmmm, I think the easiest way is to use row_number():

select id
from (select t.*, row_number() over (order by id) as seqnum
      from t
      where t.x = y
     ) t
where (seqnum % 200) in (0, 1);

编辑：

根据您的评论：

select min(id) as startid, max(id) as endid
from (select t.*,
             floor((row_number() over (order by id) - 1) / 200) as grp
      from t
      where t.x = y
     ) t
group by grp;

这篇关于SQL范围组的开始和结束ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！