星空网 > 软件开发 > 数据库

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

 
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

sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

出口日本注意!4月1日起不再享受普惠制,关税普涨3%!:https://www.ikjzd.com/articles/20142
亚马逊新手卖家必看-站内CPC广告分析:https://www.ikjzd.com/articles/20145
321电商早报327丨苏宁与ITA签订合作协议:https://www.ikjzd.com/articles/20147
出口这些国家的注意了!否则会引起货物滞留!:https://www.ikjzd.com/articles/20149
平台卖家试验独立站,Shopify or Ueeshop?:https://www.ikjzd.com/articles/20150
一个网红蜂箱,如何爆卖全球?:https://www.ikjzd.com/articles/20151
请问西安及周边5日游怎么安排?:https://www.vstour.cn/a/364173.html
图策全国免景点门票 预订景区门票优惠:https://www.vstour.cn/a/364174.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流