将行分组为 5 组 [英] Group rows into sets of 5
问题描述
Col1
----------
1
2
3
4....all the way to 27
我想添加第二列,为 5 组分配一个数字.
I want to add a second column that assigns a number to groups of 5.
Col1 Col2
----- ------
1 1
2 1
3 1
4 1
5 1
6 2
7 2
8 2...and so on
第 6 组应该有 2 行.
The 6th group should have 2 rows in it.
NTILE 无法实现我想要的效果,因为 NTILE 处理不能被整数整除的组的方式.
NTILE doesn't accomplish what I want because of the way NTILE handles the groups if they aren't divisible by the integer.
如果分区中的行数不能被 integer_expression 整除,这将导致两个大小相差一个成员的组.较大的组按照 OVER 子句指定的顺序排在较小的组之前.例如,如果总行数为 53,组数为 5,则前三组将有 11 行,其余两个组将各有 10 行.另一方面,如果总行数可被组数整除,则行将均匀分布在组中.例如,如果总行数为 50,并且有 5 个组,则每个桶将包含 10 行.
这在这个 SQL Fiddle 中得到了清楚的证明.第 4、5、6 组各有 4 行,而其余的有 5 行.我已经开始了一些解决方案,但它们越来越长,我觉得我错过了一些东西,这可以在一行中完成.
This is clearly demonstrated in this SQL Fiddle. Groups 4, 5, 6 each have 4 rows while the rest have 5. I have some started some solutions but they were getting lengthy and I feel like I'm missing something and that this could be done in a single line.
推荐答案
你可以使用这个:
;WITH CTE AS
(
SELECT col1,
RN = ROW_NUMBER() OVER(ORDER BY col1)
FROM TableA
)
SELECT col1, (RN-1)/5+1 col2
FROM CTE;
在您的示例数据中,col1
是一个没有间隙的关联,因此您可以直接使用它(如果它是 INT
),而无需使用 ROW_NUMBER()代码>.但如果不是,那么这个答案也有效.这里是修改后的 sqlfiddle.
In your sample data, col1
is a correlative without gaps, so you could use it directly (if it's an INT
) without using ROW_NUMBER()
. But in the case that it isn't, then this answer works too. Here is the modified sqlfiddle.
这篇关于将行分组为 5 组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!