SQL Server でのメモ
id | qty |
---|---|
1 | 47 |
2 | 14 |
3 | 112 |
を
id | qty |
---|---|
1 | 25 |
1 | 22 |
2 | 14 |
3 | 25 |
3 | 25 |
3 | 25 |
3 | 25 |
3 | 12 |
としたい。
サンプルのクエリはテーブル作るのメンドイので、テーブル値コンストラクターで。
テーブル値コンストラクター (Transact-SQL) - SQL Server | Microsoft Docs
with [cte] as ( select [id] , qty , case when [qty] > 25 then 25 else [qty] end as [splitted_qty] , 1 as [now] , [qty] / 25 + case when [qty] % 25 = 0 then 0 else 1 end as [split_count] from (values (1, 47), (2, 14), (3, 112)) as b([id], [qty]) union all select [id] , qty , case when [now] + 1 < [split_count] then 25 else [qty] % 25 end as [splitted_qty] , [now] + 1 as [now] , [split_count] from cte where [now] + 1 <= [split_count] ) select * from [cte] order by [id], [now]
再帰でやってるので上限超える場合は maxrecursion でよしなに。。
WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs