用户抽奖模块
award_gift_record 是用户抽奖记录表.
其中actiontime 是抽奖时间.
AwardGiftID表示中奖的礼物ID. -1表示没有中奖
awardactId 表示活动ID

需求:查询每分钟抽奖人数和中奖人数

createtablenums(idintnotnullprimary key);

delimiter $$
createprocedure pCreateNums(cntint)
begin
declare sintdefault1;
truncatetablenums;
while s<=cnt do
insertintonumsselects;
sets=s+1;
endwhile;
end$$
delimiter;

delimiter $$
createprocedure pFastCreateNums(cntint)
begin
declare sintdefault1;
truncatetablenums;
insertintonumsselects;
while s*2<=cnt do
insertintonumsselectid+sfromnums;
sets=s*2;
endwhile;
end$$
delimiter;
初始化数字辅助表
call pFastCreateNums(100000);

数字辅助表详见:
http://blog.itpub.net/29254281/viewspace-1362897/

思路就是用数字辅助表, 活动期间每分钟都生成一条记录.然后用左连接 匹配有抽奖中奖的记录

selectif(mod(result.id,16)=0,'汇总','')汇总,result.starttime,result.endtime,result.`中奖数量`,result.`抽奖数量`from(selectn.id,when@starttime=''then@starttime:=starttimeend,whenmod(n.id,16)!=0then@endtime:=endtimeend,if(mod(n.id,16)!=0,when@c1=-1then@c1:=`中奖数量`else@c1:=@c1+`中奖数量`end,''),if(mod(n.id,16)!=0,when@c2=-1then@c2:=`抽奖数量`else@c2:=@c2+`抽奖数量`end,''),whenmod(n.id,16)=0then@starttimeelsestarttimeendstarttime,whenmod(n.id,16)=0then@endtimeelseendtimeendendtime,whenmod(n.id,16)=0then@c1else`中奖数量`end`中奖数量`,whenmod(n.id,16)=0then@c2else`抽奖数量`end`抽奖数量`,whenmod(n.id,16)=0then@starttime:=''elsenullend,whenmod(n.id,16)=0then@endtime:=''elsenullend,whenmod(n.id,16)=0then@c1:=-1elsenullend,whenmod(n.id,16)=0then@c2:=-1elsenullendfromnumsnjoin(selectt3.*,whenmod(@rn+1,16)=0then@rn:=@rn+2else@rn:=@rn+1endrnfrom(selectstarttime,endtime,ifnull(`中奖数量`,0)`中奖数量`,ifnull(`抽奖数量`,0)`抽奖数量`from(selectid,'2017-12-2109:30:00'+interval(id-1)minutestarttime,'2017-12-2109:30:59'+interval(id-1)minuteendtimefromnums,(select@rn:=0,@starttime:='',@endtime:='',@c1:=-1,@c2:=-1)varswhereid<=10000AND('2017-12-2109:30:00'+interval(id-1)minute)<=(selectmax(actiontime)+interval'15'minuteFROMaward_gift_recordWHEREawardactId=235))t1join(SELECTdate_format(actiontime,'%Y-%m-%d%H:%i:00')时间,whenAwardGiftID!=-1then1elsenullend)中奖数量,FROMaward_gift_recordWHEREawardactId=235groupbydate_format(actiontime,'%Y-%m-%d%H:%i:00'))t2on(t2.时间betweent1.starttimeandendtime)groupbystarttime,endtimeorderbystarttime)t3)t4on(n.id=t4.rn)wheren.id<=(selectceil(timestampdiff(MINUTE,min(actiontime),max(actiontime)+interval'15'minute)/15*16)+15FROMaward_gift_recordWHEREawardactId=235))result;


这里最核心的是,每15行生成一行,然后用自定义变量填充生成行.

在需求二的结果上 添加行号。这个行号每到 模16 就 加二。
case when mod(@rn+1,16)=0 then @rn:=@rn+2 else @rn:=@rn+1 end rn

这样行号会是这样
...
14
15
17
18

中间把16 空过去.
这样再用数字辅助表左连接这个结果。 就可以每15行多生成一行了。

最后通过自定义变量的运算,填充生成的行即可。