SQL选择每X行递增批号

Question

我正在尝试编写一个SQL SELECT查询，该查询的批号列每5行递增一次，我尝试在动态行号上使用hacky整数除法，但无法绕过第一个批号增量始终为4行而不是5行(见图)。

RowNumber 1-5应该是1，6-10应该是2，等等。

我有什么办法做到这一点吗？

select * ROW_NUMBER() OVER (Order by (select 0)) AS RowNumber,
1 + convert(int, (ROW_NUMBER() OVER (Order by (select 0)) / 5)) as BatchNumber,
* from WorkQueue

​

​

Answer 1

这应该是可行的：

select ROW_NUMBER() OVER (Order by (select 0)) AS RowNumber,
(case when convert(int, (ROW_NUMBER() OVER (Order by (select 0)) % 5))=0 then 0 else 1 end) + convert(int, (ROW_NUMBER() OVER (Order by (select 0)) / 5)) as BatchNumber,
* from WorkQueue

Answer 2

试试这个：

SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNumber,
        CEILING(CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS DECIMAL(10,2))/5) AS BatchNo, *
FROM    WorkQueue