我会定义一个表包含序列号,说1
要1000
和加入该表(除非你的数据库支持生成甲骨文使用类似查询这些号码CONNECT BY
):
表号
n
1
2
3
...
我使用Oracle尝试了以下查询(也应与Tsql一起使用):
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
它返回
Blue 1
Red 2
Red 2
Red 1
Yellow 2
Yellow 1