SQL查询在给定的时间间隔内分割数字

Question

我有一个数字（比如100,000）。我想在15,000到19,900的给定时间间隔内分配这个数字。我预计结果将在15,000到19,900之间。我需要一个SQL查询/函数来完成这项任务。

例子： 如果我需要15000个19900的结果区间之间的分裂100000应该是这样的：

15100.00 
16750.00 
19365.22 
18254.85 
15987.65 
14542.28 

请看到所有这些值的总和等于事先100000个 感谢。

Answer 1

如果最后选择的数字不需要在您指定的范围内（如您的示例所示），以下操作会返回一些随机数字，总和为给定的总数。

DECLARE @LowBound FLOAT 
DECLARE @HighBound FLOAT 
DECLARE @Total FLOAT 

SET @LowBound = 15000 
SET @HighBound = 19900 
SET @Total = 100000 

;WITH q AS (
    SELECT [Value] = @LowBound + (@HighBound - @LowBound) * RAND(CHECKSUM(NEWID())) 
      , [RunningTotal] = CAST(0.00 AS FLOAT) 
    UNION ALL 
    SELECT [Value] = @LowBound + (@HighBound - @LowBound) * RAND(CHECKSUM(NEWID())) 
      , [RunningTotal] = CAST((q.[RunningTotal] + [Value]) AS FLOAT) 
    FROM q   
    WHERE q.[RunningTotal] < @Total 
) 
SELECT CASE WHEN [RunningTotal] < @Total 
       THEN [Value] 
       ELSE [Value] - ([RunningTotal] - @Total) 
     END AS Result 
FROM (
      SELECT [Value] 
        , [RunningTotal] = [Value] + [RunningTotal] 
      FROM q 
     ) q 
WHERE [RunningTotal] - [Value] <= @Total