随机数生成器-在to中，创建一个随机数矩阵，其中每行的值之和为X？

Question

如何生成一个随机数矩阵，其中每行的值在the？中相加为X。

解决矩阵应该是动态的：

1. 用户可以指定结果中要返回的列数。
2. 用户可以指定结果中要返回的行数。
3. 每一行的总和必须为X(例如。1)

创建proc RandomNumberGenerator ( @rows，@cols，@rowsumtotal RandomNumberGenerator)作为.

Answer 1

首先创建一个UDF..。

CREATE FUNCTION [dbo].[_ex_fn_SplitToTable] (@str varchar(5000), @sep varchar(1) = null)                                

RETURNS @ReturnVal table (n int, s varchar(5000))                               

AS                              
/*                              
Alpha Test                              
-----------                             
select * from [dbo].[_ex_fn_SplitToTable_t2]('a b c d e',' ')                               
*/                              
BEGIN                               
if @sep =  ' '                              
begin                               
    set @sep = CHAR(167)                            
    set @str = REPLACE(@str,' ',CHAR(167))                          
end                             

    declare @str2 varchar(5000)                         
    declare @sep2 varchar(1)                            
    if LEN(ISNULL(@sep,'')) = 0                         
    begin                           
        declare @i int                      
        set @i = 0                      

        set @str2 = ''                      
        declare @char varchar(1)                        
        startloop:                      
            set @i += 1                 
            --print @i                  
            set @char = substring(@str,@i,1)                    
            set @str2 = @str2 + @char + ','                 
            if LEN(@str) <= @i                  
                goto exitloop               
            goto startloop                  
        exitloop:                       
        set @str2 = left(@str2,LEN(@str2) - 1)                      
        set @sep2 = ','                     
        --print @str2                       

    end                             
    else                            
    begin                           
        set @str2 = @str                        
        set @sep2 = @sep                        
    end                         

    ;WITH Pieces(n, start, stop) AS (                               

      SELECT 1, 1, CHARINDEX(@sep2, @str2)                              

      UNION ALL                                 

      SELECT n + 1, stop + 1, CHARINDEX(@sep2, @str2, stop + 1)                                 

      FROM Pieces                               

      WHERE stop > 0                                

    )                               

    insert into @ReturnVal(n,s)                                 
    SELECT n,                               
      SUBSTRING(@str2, start, CASE WHEN stop > 0 THEN stop-start ELSE 5000 END) AS s                                
    FROM Pieces option (maxrecursion 32767)                                 


    RETURN                              

END                             




GO                              

然后，创建这个存储的过程..。

CREATE PROC [dbo].[RandomNumberGenerator]                               
    (                           
        @Pockets int = 6,                       
        @SumTo float = 100,                     
        @i_iterations int = 100                     
    )                           
/*                              
ALPHA TEST                              
----------                              
exec RandomNumberGenerator 10, 100, 500                             

*/                              
AS                              


if object_id('tempdb..#_Random_00') is not null drop table #_Random_00                              


declare @columnstring varchar(max) = (SELECT REPLICATE('c ',@Pockets) as Underline)                             
print @columnstring                             

if object_id('tempdb..#_Random_columns') is not null drop table #_Random_columns                                
select s+CONVERT(varchar,dbo.PadLeft(convert(varchar,n),'0',3)) cols                                
into #_Random_columns                               
from [dbo].[_ex_fn_SplitToTable](@columnstring,' ') where LEN(s) > 0                                

                    -- ===========================          
                    --select * from #_Random_columns            
                    -- ===========================          

declare @columns_sql varchar(max)                               
set @columns_sql =                              
        (                       
            select distinct                     
              stuff((SELECT distinct + cast(cols as varchar(50)) +  ' float, '                  
                       FROM (           
                        select cols     
                        from #_Random_columns       
                        ) t2        
                       --where t2.n = t1.n          
                       FOR XML PATH('')),3,0,'')            
            from (                  
                select cols             
                from #_Random_columns               
                ) t1                
        )                       

set @columns_sql = LEFT(@columns_sql,LEN(@columns_sql) - 1)                             
print @columns_sql                              


declare @sql varchar(max)                               
set @sql = 'if object_id(''tempdb..##_proctable_Random_01'') is not null drop table ##_proctable_Random_01 '                                
print @sql                              
execute(@sql)                               
set @sql = 'create table ##_proctable_Random_01 (rowid int,' + @columns_sql + ')'                               
print @sql                              
execute(@sql)                               

declare @TotalOfRand float                              
declare @i_inner int                                
declare @i_outer int                                
set @i_outer = 0                                
start_outer:                                
    set @i_outer = @i_outer + 1                         

    set @i_inner = 0                            
    declare @sumstring varchar(max)                         
    set @sumstring = ''                         
    start_inner:                            
        set @i_inner = @i_inner+1                       
        set @sumstring = @sumstring + CONVERT(varchar, rand()) + ','                        
        if @i_inner >= @Pockets                     
            goto exit_inner                 
        goto start_inner                        
    exit_inner:                         
    set @TotalOfRand = ( select sum(convert(float,s)) from dbo._ex_fn_SplitToTable(@sumstring,',') )                            

    declare @sumstring_quotient varchar(max)                            
    set @sumstring_quotient = replace(@sumstring,',', '/' + Convert(varchar,@TotalOfRand) + '*' + convert(varchar,@SumTo) + ',')                            
    set @sumstring_quotient = LEFT(@sumstring_quotient,len(@sumstring_quotient) - 1)                            
    print @sumstring_quotient                           
    set @sql = '                            
    insert into ##_proctable_Random_01                          
    select                          
        ( select count(*) + 1 from ##_proctable_Random_01 ) rowid,' + @sumstring_quotient                       
    execute(@sql)                           
    if @i_outer >= @i_iterations                            
        goto exit_outer                     
    goto start_outer                            
exit_outer:                             

select * from ##_proctable_Random_01                                
drop table ##_proctable_Random_01                               



GO