生成指定几个数的和为某个数,随机出来的数要小一某个数 并且大于某个数,可以为0如:N=9 ---合计数i=4 ---生成笔数
max=3 ----生成数的最大值,超过此数要重新生成
min=1 ----生成数的最小值,小于此数要重新生成生成出来的数可以是小数组合可能为:
3 3 3 0
2 2 3 2
1.3 2.6 3 2.1
max=3 ----生成数的最大值,超过此数要重新生成
min=1 ----生成数的最小值,小于此数要重新生成生成出来的数可以是小数组合可能为:
3 3 3 0
2 2 3 2
1.3 2.6 3 2.1
生成[1-3]的数你要四个数的和为9麻烦
if object_id('[tb]') is not null drop table [tb]
create table [tb]([col] numeric(2,1))
insert [tb]
select 0 union all
select 1 union all
select 1.3 union all
select 2 union all
select 2.1 union all
select 2.6 union all
select 3--select * from [tb] --tb里面的数为指定的数declare @n int
set @n=3--sql2005 top 里面参数可以用动态 sql2000用execselect top(@n) t1col , t2col , t3col , t4col from
(
select t1.col as t1col , t2.col as t2col , t3.col as t3col , t4.col as t4col ,total=t1.col+t2.col+t3.col+t4.col from
(select col from tb) t1,
(select col from tb) t2,
(select col from tb) t3,
(select col from tb) t4 ) tmp
where total=9 order by newid()
/*
t1col t2col t3col t4col
--------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
3.0 1.0 3.0 2.0
2.0 2.0 3.0 2.0
3.0 2.0 2.0 2.0
*/
--tryselect ceiling(rand()*3)
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
ID INT
,ID2 INT
,VAL NUMERIC(19,2)
)
GO
DECLARE @N NUMERIC(19,6),@I INT,@MAX NUMERIC(19,2),@MIN NUMERIC(19,2),@J INT,@K INT,@ID INT,@ID2 INT
SELECT @N=9,@I=4,@MAX=3,@MIN=1
SELECT @ID=ISNULL(MAX(ID),0)+1,@ID2=1 FROM TBWHILE @ID2<=@I
BEGIN
IF (SELECT COUNT(VAL) FROM TB WHERE ID=@ID AND ID2=@ID2)<@I-1
BEGIN
DECLARE @TEMP NUMERIC(19,2)
SELECT @TEMP=CONVERT(NUMERIC(19,2),RAND()*(@MAX-@MIN)+@MIN)
IF (SELECT ISNULL(SUM(VAL),0)+@TEMP FROM TB WHERE ID=@ID AND ID2=@ID2)<@N
INSERT INTO TB
SELECT @ID,@ID2,@TEMP
ELSE
INSERT INTO TB
SELECT @ID,@ID2,0
END
ELSE
BEGIN
INSERT INTO TB
SELECT @ID,@ID2,@N-ISNULL(SUM(VAL),0) FROM TB WHERE ID=@ID AND ID2=@ID2
END
SELECT @ID2=@ID2+1
END
SELECT * FROM TB
/*
1 1 2.01
1 2 1.45
1 3 1.63
1 4 1.22
*/
declare @a numeric(4,1)
, @j int
, @i INT
, @n numeric(4,1)
, @max numeric(4,1)
, @min numeric(4,1)
, @sum numeric(4,1)
declare @tb table(number numeric(4,1))
set @a=13.2
set @i=23.0
set @max=3
set @min=1set @sum=0set @j=1
while @j<@i
begin
set @n=rand()*@max+@min
if len(@sum)=0
set @sum=0
if @a=@sum or @a-@sum<=@max
set @n=0
if (@a-@sum-@n)/@i<=@max and @n<=@max and @sum+@n<@a
begin
insert into @tb select @n
set @i=@i-1
end
select @sum=isnull(sum(number),0) from @tb
end
insert into @tb select @a-@sumselect * from @tb
http://blog.csdn.net/maco_wang/archive/2009/02/23/3925482.aspx