ALTER proc pr_zhanglei_test1/*功能描述:根据t_zhanglei_test1中perc设置的概率,取到相应数据old_id*/asdeclare @percent_total int, @max_id int, @min_id intcreate table #t_zhanglei_temp --临时表存储变化表t_zhanglei_test1中total>0的数据(id int identity(1,1) not null, old_id int not null,name varchar(50) not null,total int not null,perc int not null) insert into #t_zhanglei_temp(old_id,name,total,perc)select id,name,total,perc from t_zhanglei_test1where total>0;if exists(select count(1) from #t_zhanglei_temp)begin declare @perc_temp int select @max_id=max(id),@min_id=min(id),@percent_total=sum(perc) from #t_zhanglei_temp create table #zhanglei_temp( --存储变化权值区间 id int not null, old_id int not null, start_num int not null, end_num int not null ) insert into #zhanglei_temp(id,old_id,start_num,end_num) select @min_id,old_id,1,perc from #t_zhanglei_temp where id=@min_id; declare @id int declare @max_end_num int, @old_id int while @min_id<@max_id begin set @min_id=@min_id+1; select @perc_temp =perc,@old_id=old_id from #t_zhanglei_temp where id=@min_id; select @max_end_num=max(end_num) from #zhanglei_temp insert into #zhanglei_temp(id,old_id,start_num,end_num) select @min_id,@old_id,@max_end_num+1,@max_end_num+@perc_temp; end declare @max_random int, @random_temp int, @return_id int select @max_random=end_num from #zhanglei_temp; set @random_temp=cast(ceiling(rand() * @max_random) as int); select @return_id=old_id from #zhanglei_temp where @random_temp between start_num and end_num update t_total set total=total+1 where id=@return_id; if @@rowcount=0 begin insert into t_total(id,total) values(@return_id,1); end end--相关表结构CREATE TABLE [t_zhanglei_test1] ( [id] [int] NOT NULL , [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [total] [int] NOT NULL , [perc] [int] NOT NULL --本调数据出现的概率) ON [PRIMARY]GO--插入测试数据insert into t_zhanglei_test1select 111,'测试一',8,10union allselect 222,'测试二',8,20union allselect 333,'测试三',8,70GOCREATE TABLE [t_total] ( [id] [int] NOT NULL , [total] [bigint] NOT NULL ) ON [PRIMARY]GO-- 调取存储declare @i intset @i=0while @i<10000begin exec pr_zhanglei_test1 set @i=@i+1end--查看效果select * from t_total
原标题:sqlserver中根据表中的配置概率取到数据
关键词:sql