34,576
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE testgg (a INT IDENTITY,b INT)
go
DECLARE @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=3
SET ROWCOUNT @N
INSERT testgg SELECT 0 FROM syscolumns s
SET @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
*/
declare @n int, @s int, @i int
select @n = 5, @s = 30, @i = 3
declare @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
end
set @rand = @s - @sum
print @rand
/*
4
8
5
7
6
*/
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();
GO
EXEC p_GetRandomNumber 5,30,5;
GO
DROP PROC p_GetRandomNumber
封装成存储过程.
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();
GO
EXEC p_GetRandomNumber 5,80,10;
GO
DROP PROC p_GetRandomNumber
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;
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();
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;
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();