Generate rows based on division with remainder

2lpgd968  于 2023-02-28  发布在  其他
关注(0)|答案(2)|浏览(108)

What is the best to generate rows based on division and remainer?

declare @Total int = 203;
declare @Batch int = 100;

I did some complicated query with T-SQL but it is not working.

Result should be:

100
100
3
kqlmhetl

kqlmhetl1#

Assuming the latest version of SQL Server (2022 at the time of writing), as no indication of version has been given, one method would be to use GENERATE_SERIES . You can use this to generate one row per batch, and then return the modulo when the upper value is greater than than the total:

DECLARE @Total int = 203;
DECLARE @Batch int = 100;

SELECT CASE WHEN GS.[value] + @Batch <= @Total THEN @Batch
            ELSE @Total % @Batch
       END
FROM GENERATE_SERIES(0,@Total,@Batch) GS;
uelo1irk

uelo1irk2#

If you don't have SQL2022 here's a way of building a recursive CTE and then using that data to build the result set:

DECLARE @Total INT = 305;
DECLARE @Batch INT = 100;

WITH CTE
AS (
    SELECT @Batch AS Num, 1 AS x
    UNION ALL
    SELECT CTE.Num + @Batch, CTE.x + 1 FROM CTE WHERE CTE.Num + @Batch < @Total
)
SELECT CTE.Num / CTE.x FROM CTE
UNION ALL
SELECT @Total - MAX(CTE.Num)FROM CTE;

It initially builds the CTE in steps of @Batch but as a total (e.g. 100 then 200) up until the next iteration would take you over the total. I added a column to iterate by counts of 1 so that we can then later divide Num by x to get each value back to the Batch size.

Finally in the select there's a UNION to get the remainder which is the total minus the maximum value the CTE iterated to

相关问题