sqlserver中根據表中的配置概率取到數據

create   proc pr_zhanglei_test1
/*功能描述：
根據t_zhanglei_test1中perc設置的概率，取到相應數據old_id
*/
as
declare @percent_total  int,
    @max_id int,
    @min_id int


create 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_test1
where     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_test1
select 111,'測試一',8,10
union all
select 222,'測試二',8,20
union all
select 333,'測試三',8,70

GO

CREATE TABLE [t_total] (
    [id] [int] NOT NULL ,
    [total] [bigint] NOT NULL 
) ON [PRIMARY]
GO


-- 調取存儲
declare @i int
set @i=0
while @i<10000
begin
    exec pr_zhanglei_test1
    
    set @i=@i+1
end

--查看效果
select * from t_total