with wang1 as (select num1=cast(rand()*10 as int)), wang2 as (select num1,num2=cast(rand()*(30-num1) as int)from wang1), wang3 as (select num1,num2,num3=cast(rand()*(30-num1-num2) as int)from wang2), wang4 as (select num1,num2,num3,num4=cast(rand()*(30-num1-num2-num3) as int)from wang3), wang5 as (select num1,num2,num3,num4,num5=30-num1-num2-num3-num4 from wang4) select * from wang5num1 num2 num3 num4 num5 ----------- ----------- ----------- ----------- ----------- 2 16 2 2 8(1 行受影响)
我的是sql2K,大哥这样我执行不了的。
1楼的代码改2000就这样改吧.DECLARE @num1 INT,@num2 INT,@num3 INT,@num4 INT,@num5 INTSELECT @num1=cast(rand()*10 as int), @num2=cast(rand()*(30-@num1) as int), @num3=cast(rand()*(30-@num1-@num2) as int), @num4=cast(rand()*(30-@num1-@num2-@num3) as int), @num5=30-@num1-@num2-@num3-@num4
--这样试试:SET NOCOUNT ON;DECLARE @tb TABLE(number INT);DECLARE @number INT; DECLARE @N INT,@S INT; DECLARE @i INT; DECLARE @total INT;SELECT @N=5,@S=30,@i=1; SET @number = RAND()*(@S-@N+1)+1 INSERT @tb VALUES(@number)WHILE @i < @N-1 BEGIN SET @total=(SELECT SUM(number) FROM @tb); SET @number = RAND()*(@S-@N-@total+@i)+1; INSERT @tb VALUES(@number);
SET @i=@i+1; ENDSET @total=(SELECT SUM(number) FROM @tb); INSERT @tb VALUES(@S-@total);--显示结果 SELECT SUM(number) AS total FROM @tb; SELECT * FROM @tb ORDER BY NEWID();
这样是OK的,我的要求还更复杂一点,如果有空就在帮帮忙,没空的话,我一会结贴。我的要求是还有一个基数,生成的随机数不能低于某个数 I ,如N=5 S=30 I=3 就是生成出来的5个数都不能低于3
要大于3的话,把每个随机数改成 3+27*rand()
--这样再试试.SET NOCOUNT ON;DECLARE @tb TABLE(number INT);DECLARE @number INT; DECLARE @N INT,@S INT; DECLARE @i INT; DECLARE @total INT; DECLARE @M INT--N:个数,S:总数,M:不小于基数 SELECT @N=5,@S=80,@M=10; SET @i=1; SET @number = RAND()*(@S/@N)+@M INSERT @tb VALUES(@number)WHILE @i < @N-1 BEGIN SET @total=(SELECT SUM(number) FROM @tb);
SET @number = (RAND()*(@S/(@N+@M)))+@M; INSERT @tb VALUES(@number); SET @i=@i+1; ENDSET @total=(SELECT SUM(number) FROM @tb); INSERT @tb VALUES(@S-@total);--显示结果 SELECT SUM(number) AS total FROM @tb; SELECT * FROM @tb ORDER BY NEWID();
封装成存储过程.CREATE PROC p_GetRandomNumber @N INT,@S INT,@M INT AS SET NOCOUNT ON; DECLARE @tb TABLE(number INT); DECLARE @number INT; --DECLARE @N INT,@S INT; DECLARE @i INT; DECLARE @total INT; --DECLARE @M INT --N:个数,S:总数,M:不小于基数 --SELECT @N=10,@S=80,@M=10; IF @N*@M>@S BEGIN RAISERROR('数字组合错误',16,1); RETURN; END SET @i=1; SET @number = RAND()*(@S/@N)+@M INSERT @tb VALUES(@number) WHILE @i < @N-1 BEGIN SET @total=(SELECT SUM(number) FROM @tb);
SET @number = (RAND()*(@S/(@N+@M)))+@M; INSERT @tb VALUES(@number); SET @i=@i+1; END SET @total=(SELECT SUM(number) FROM @tb); INSERT @tb VALUES(@S-@total); --显示结果 --SELECT SUM(number) AS total FROM @tb; SELECT * FROM @tb ORDER BY NEWID(); GOEXEC p_GetRandomNumber 5,80,10;GO DROP PROC p_GetRandomNumber
sorry,写错这样再试试:CREATE PROC p_GetRandomNumber @N INT,@S INT,@M INT AS SET NOCOUNT ON; DECLARE @tb TABLE(number INT); DECLARE @number INT; --DECLARE @N INT,@S INT; DECLARE @i INT; DECLARE @total INT; --DECLARE @M INT --N:个数,S:总数,M:不小于基数 --SELECT @N=10,@S=80,@M=10; IF @N*@M>@S BEGIN RAISERROR('数字组合错误',16,1); RETURN; END SET @i=1; SET @number = RAND()*(@S/@N)+@M INSERT @tb VALUES(@number) WHILE @i < @N-1 BEGIN SET @total=(SELECT SUM(number) FROM @tb);
SET @number = (RAND()*(@S-@total-(@N-@i)*@M))+@M; INSERT @tb VALUES(@number); SET @i=@i+1; END SET @total=(SELECT SUM(number) FROM @tb); INSERT @tb VALUES(@S-@total); --显示结果 --SELECT SUM(number) AS total FROM @tb; SELECT * FROM @tb ORDER BY NEWID(); GOEXEC p_GetRandomNumber 5,30,5;GO DROP PROC p_GetRandomNumber
wang1 as (select num1=cast(rand()*10 as int)),
wang2 as (select num1,num2=cast(rand()*(30-num1) as int)from wang1),
wang3 as (select num1,num2,num3=cast(rand()*(30-num1-num2) as int)from wang2),
wang4 as (select num1,num2,num3,num4=cast(rand()*(30-num1-num2-num3) as int)from wang3),
wang5 as (select num1,num2,num3,num4,num5=30-num1-num2-num3-num4 from wang4)
select * from wang5num1 num2 num3 num4 num5
----------- ----------- ----------- ----------- -----------
2 16 2 2 8(1 行受影响)
@num1=cast(rand()*10 as int),
@num2=cast(rand()*(30-@num1) as int),
@num3=cast(rand()*(30-@num1-@num2) as int),
@num4=cast(rand()*(30-@num1-@num2-@num3) as int),
@num5=30-@num1-@num2-@num3-@num4
DECLARE @N INT,@S INT;
DECLARE @i INT;
DECLARE @total INT;SELECT @N=5,@S=30,@i=1;
SET @number = RAND()*(@S-@N+1)+1
INSERT @tb VALUES(@number)WHILE @i < @N-1
BEGIN
SET @total=(SELECT SUM(number) FROM @tb); SET @number = RAND()*(@S-@N-@total+@i)+1;
INSERT @tb VALUES(@number);
SET @i=@i+1;
ENDSET @total=(SELECT SUM(number) FROM @tb);
INSERT @tb VALUES(@S-@total);--显示结果
SELECT SUM(number) AS total FROM @tb;
SELECT * FROM @tb ORDER BY NEWID();
就是生成出来的5个数都不能低于3
要大于3的话,把每个随机数改成 3+27*rand()
DECLARE @N INT,@S INT;
DECLARE @i INT;
DECLARE @total INT;
DECLARE @M INT--N:个数,S:总数,M:不小于基数
SELECT @N=5,@S=80,@M=10;
SET @i=1;
SET @number = RAND()*(@S/@N)+@M
INSERT @tb VALUES(@number)WHILE @i < @N-1
BEGIN
SET @total=(SELECT SUM(number) FROM @tb);
SET @number = (RAND()*(@S/(@N+@M)))+@M;
INSERT @tb VALUES(@number);
SET @i=@i+1;
ENDSET @total=(SELECT SUM(number) FROM @tb);
INSERT @tb VALUES(@S-@total);--显示结果
SELECT SUM(number) AS total FROM @tb;
SELECT * FROM @tb ORDER BY NEWID();
AS
SET NOCOUNT ON; DECLARE @tb TABLE(number INT); DECLARE @number INT;
--DECLARE @N INT,@S INT;
DECLARE @i INT;
DECLARE @total INT;
--DECLARE @M INT --N:个数,S:总数,M:不小于基数
--SELECT @N=10,@S=80,@M=10; IF @N*@M>@S
BEGIN
RAISERROR('数字组合错误',16,1);
RETURN;
END SET @i=1;
SET @number = RAND()*(@S/@N)+@M
INSERT @tb VALUES(@number) WHILE @i < @N-1
BEGIN
SET @total=(SELECT SUM(number) FROM @tb);
SET @number = (RAND()*(@S/(@N+@M)))+@M;
INSERT @tb VALUES(@number);
SET @i=@i+1;
END SET @total=(SELECT SUM(number) FROM @tb);
INSERT @tb VALUES(@S-@total); --显示结果
--SELECT SUM(number) AS total FROM @tb;
SELECT * FROM @tb ORDER BY NEWID();
GOEXEC p_GetRandomNumber 5,80,10;GO
DROP PROC p_GetRandomNumber
1,能不能重复;(这N个数里面同一数值可不可以出现多次,26+1+1+1+1=30,这种组合是否有效。)
2,这N个随机数有没有范围限制;(例如楼主后来补充的最小值为3,或最大值--当然是在这N个数和以内的更小的范围。像 3<N<20)
select @n = 5, @s = 30, @i = 3declare @cyc int, @rand int, @sum int
select @cyc = 0, @sum = 0
while @cyc < @n-1
begin
set @rand = rand() * (@s - @sum - (@n-@cyc)*@i) + 3
set @sum = @sum + @rand
print @rand
set @cyc = @cyc + 1
endset @rand = @s - @sum
print @rand/*
4
8
5
7
6
*/
sorry,写错这样再试试:CREATE PROC p_GetRandomNumber @N INT,@S INT,@M INT
AS
SET NOCOUNT ON; DECLARE @tb TABLE(number INT); DECLARE @number INT;
--DECLARE @N INT,@S INT;
DECLARE @i INT;
DECLARE @total INT;
--DECLARE @M INT --N:个数,S:总数,M:不小于基数
--SELECT @N=10,@S=80,@M=10; IF @N*@M>@S
BEGIN
RAISERROR('数字组合错误',16,1);
RETURN;
END SET @i=1;
SET @number = RAND()*(@S/@N)+@M
INSERT @tb VALUES(@number) WHILE @i < @N-1
BEGIN
SET @total=(SELECT SUM(number) FROM @tb);
SET @number = (RAND()*(@S-@total-(@N-@i)*@M))+@M;
INSERT @tb VALUES(@number);
SET @i=@i+1;
END SET @total=(SELECT SUM(number) FROM @tb);
INSERT @tb VALUES(@S-@total); --显示结果
--SELECT SUM(number) AS total FROM @tb;
SELECT * FROM @tb ORDER BY NEWID();
GOEXEC p_GetRandomNumber 5,30,5;GO
DROP PROC p_GetRandomNumber
goDECLARE @Num VARCHAR(20),@C INT,@Base INT,@N int
DECLARE @i INT
DECLARE @sql1 VARCHAR(1000),@sql2 VARCHAR(1000),@sql3 VARCHAR(1000),@sql4 VARCHAR(1000)SELECT @Num='30',@N=cast(@Num as int),@C=5,@Base=3SET ROWCOUNT @NINSERT testgg SELECT 0 FROM syscolumns sSET @i=0
WHILE @i<@C
BEGIN
SELECT
@sql1=isnull(@sql1+',','')+CHAR(65+@i)+'.a',
@sql2=isnull(@sql2+',','')+' testgg '+CHAR(65+@i),
@sql3=isnull(@sql3+'+','')+CHAR(65+@i)+'.a',
@sql4=ISNULL(@sql4+' and ','')+CHAR(65+@i)+'.a>'+LTRIM(@Base)
SET @i=@i+1
END
EXEC('select '+@sql1+' from '+@sql2+' where '+@sql3 +'='+@Num+' and '+@sql4)
DROP TABLE testgg
--result
/*a a a a a
----------- ----------- ----------- ----------- -----------
4 14 4 4 4
4 13 5 4 4
4 12 6 4 4
4 11 7 4 4
4 10 8 4 4
4 9 9 4 4
4 8 10 4 4
4 7 11 4 4
4 6 12 4 4
4 5 13 4 4
4 4 14 4 4
4 13 4 5 4
4 12 5 5 4
4 11 6 5 4
4 10 7 5 4
4 9 8 5 4
4 8 9 5 4
4 7 10 5 4
4 6 11 5 4
4 5 12 5 4
4 4 13 5 4
4 12 4 6 4
4 11 5 6 4
4 10 6 6 4
4 9 7 6 4
4 8 8 6 4
4 7 9 6 4
4 6 10 6 4
4 5 11 6 4
4 4 12 6 4
*/