SQL SELECT每X行增量批号 [英] SQL SELECT incremental batch number every X rows
本文介绍了SQL SELECT每X行增量批号的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试编写一个 SQL SELECT 查询,该查询具有一个每 5 行递增的批号列,我尝试在动态行号上使用 hacky 整数除法,但无法绕过第一个批号增量始终为 4 行而不是 5 行(见图).
I'm trying to write a SQL SELECT query that has a batch number column that increments every 5 rows, I've tried using a hacky integer division on a dynamic row number, but can't get around the first batch number increment always being 4 rows instead of 5 (see image).
RowNumber 1-5 应该是 1,6-10 应该是 2 等等.
RowNumber 1-5 should be 1, 6-10 should be 2 etc..
有什么想法可以实现这一目标吗?
Any ideas how I can achieve this?
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
推荐答案
这应该有效:
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
这篇关于SQL SELECT每X行增量批号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文