按行数计数的SQL组 [英] SQL Group By Modulo of Row Count
问题描述
我有以下样本数据:
编号名称数量
1红色1
2红色3
3蓝色1
4红色1
5黄色3
因此,对于这个例子,总共有5个红色,1个蓝色和3个黄色。我正在寻找一种按颜色对它们进行分组的方法,但每个组最多有2个项目(排序并不重要)。像这样:
名称QuantityInPackage
红色2
红色2
红色1
蓝1
黄2
黄1
关于如何完成的任何建议在MS-SQL 2005上使用T-SQL?
我会定义一个包含连续数字的表,比如 1
到 1000
并加入该表(除非数据库支持在像Oracle这样的查询中使用 CONNECT生成这些数字BY
):
表格数字
n
1
2
3
...
我使用Oracle试过以下查询(也应该使用TSQL):
With summed_colors As(
选择名称,总和(数量)数量
从颜色
组合名称
)
选择
名称,
情况下n * 2-1 =数量然后1否则2结束quantityInPackage
来自summed_color s
加入nums On(n * 2-1 <=数量)
订单按名称,数量InPackage Desc
并返回
蓝色1
红色2
红色2
红色1
黄色2
黄色1
I have the following sample data:
Id Name Quantity
1 Red 1
2 Red 3
3 Blue 1
4 Red 1
5 Yellow 3
So for this example, there are a total of 5 Red, 1 Blue, and 3 Yellow. I am looking for a way to group them by Color, but with a maximum of 2 items per group (sorting is not important). Like so:
Name QuantityInPackage
Red 2
Red 2
Red 1
Blue 1
Yellow 2
Yellow 1
Any suggestions on how to accomplish this using T-SQL on MS-SQL 2005?
I would define a table containing sequential numbers, say 1
to 1000
and join that table (unless your database supports generating these numbers in the query like Oracle using CONNECT BY
):
Table num
n
1
2
3
...
I tried the following query using Oracle (should work with TSQL too):
With summed_colors As (
Select name, Sum(quantity) quantity
From colors
Group By name
)
Select
name,
Case When n*2-1 = quantity Then 1 Else 2 End quantityInPackage
From summed_colors
Join nums On ( n*2-1 <= quantity )
Order By name, quantityInPackage Desc
and it returns
Blue 1
Red 2
Red 2
Red 1
Yellow 2
Yellow 1
这篇关于按行数计数的SQL组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!