34,590
社区成员
发帖
与我相关
我的任务
分享
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 TB
WHILE @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
*/
--try
select ceiling(rand()*3)
--> 测试数据:[tb] -随机生成指定几个数的和为某个数
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用exec
select 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
*/
SELECT CEILING(RAND()*2+1)
]应该是这样才是1-3SELECT CEILING(RAND()*1+2)
生成[1-3]的数