您的位置:68399皇家赌场 > 虚拟主机 > sqlserver中依照表中的配置可能率取到数据

sqlserver中依照表中的配置可能率取到数据

发布时间:2019-05-04 07:42编辑:虚拟主机浏览(165)

    澳门皇家赌场55533网址, 

    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
    

     

    本文由68399皇家赌场发布于虚拟主机,转载请注明出处:sqlserver中依照表中的配置可能率取到数据

    关键词: 68399皇家赌场

上一篇:mysql 触发器更新当前行

下一篇:没有了