sqlserver中根据表中的配置概率取到数据

2016-12-06 19:08:54来源:cnblogs.com作者:刷屏君人点击

`create   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`