基于列值拆分行的递归CTE

Question

我有一个CTE，它返回一组项目ID和数量。我正在尝试使用另一个CTE，根据定义的容器大小将每一行拆分为多行。例如，如果容器大小被指定为20，行数量为49，我想将其拆分为2行，数量为20，一行为9。

下面是我最后被卡住的地方。对于这种情况，递归CTE是错误的选择吗？如有任何帮助，我们将不胜感激。

DECLARE @ContainerSize int = 20;
WITH ItemDetails (ItemID, Qty) AS (
-- Query that returns data like below
SELECT 29, 49
UNION ALL
SELECT 33, 64
UNION ALL
SELECT 38, 32
UNION ALL
SELECT 41, 54
),
ItemDetailsSplit(n, ItemID, Qty) AS (
SELECT  
0,
ItemID,
Qty
FROM ItemDetails
UNION ALL
SELECT
n + 1,
ItemID,
CASE WHEN Qty < (@ContainerSize * (n + 1))
THEN Qty
ELSE Qty - (@ContainerSize * (n + 1))
END AS [Qty]        
FROM ItemDetailsSplit   
WHERE ( Qty > (@ContainerSize * n) )
)
SELECT * 
FROM ItemDetailsSplit
ORDER BY ItemID, Qty DESC;

Answer 1

在不了解特定RDBMS的情况下，我有一个可与SQL Server配合使用的解决方案，它可以轻松转换到任何数据库平台。

这使用了一个数字表-这里是另一个CTE，但在生产中你会有一个永久表。

declare @ContainerSize int = 20;
with numbers (n) as (
select top(100) Row_Number() over(order by(select null)) from master.dbo.spt_values
), ItemDetails (ItemID, Qty) as (
-- Query that returns data like below
select 29, 49
union all
select 33, 64
union all
select 38, 32
union all
select 41, 54
)
select ItemID, Iif(n <= Qty / @ContainerSize, @ContainerSize, Qty % @ContainerSize) Qty
from ItemDetails d
cross apply numbers n
where n <= (Qty / @ContainerSize) + Iif(Qty % @ContainerSize = 0, 0, 1)
order by ItemID, Qty

请参阅工作DB<gt；Fiddle