现在写这种SQL纯属是小娱乐了.不过这种场景貌似挺多的.还是需要记录一下.

实验结构和数据:

create table act(
id int primary key auto_increment,
name varchar(10),
starttime timestamp not null,
endtime timestamp not null
);
INSERT INTO `act` (`id`,`name`,`starttime`,`endtime`) VALUES (1,'活动一','2015-01-01 00:00:00','2015-05-01 00:00:00');
INSERT INTO `act` (`id`,`name`,`starttime`,`endtime`) VALUES (2,'活动二','2015-02-01 00:00:00','2015-03-01 00:00:00');
INSERT INTO `act` (`id`,`name`,`starttime`,`endtime`) VALUES (3,'活动三','2015-05-01 00:00:00','2015-10-01 00:00:00');
INSERT INTO `act` (`id`,`name`,`starttime`,`endtime`) VALUES (4,'活动四','2015-08-01 00:00:00','2015-11-01 00:00:00');

需求:
查看每个月举办的活动总数.
以活动一为例,开始时间是2015-01-01,结束时间是2015-05-01
那么该活动在1月,2月,3月,4月,5月各算一次.

这个需求本质是把一个时间的范围,变成具体的记录
比如有一行 6月-9月
变成4行
6月
7月
8月
9月

这个肯定需要数字辅助表的介入了.
创建数字辅助表

create table nums(id int not null primary key);
delimiter $$
create procedure pCreateNums(cnt int)
begin
declare s int default 1;
truncate table nums;
while s<=cnt do
insert into nums select s;
set s=s+1;
end while;
end $$
delimiter ;
call pCreateNums(1000);

统计SQL:

select ts,count(*) from (
select a.*,date_format(a.starttime+interval id-1 month,'%Y-%m-%d') ts from (
select name,starttime,endtime,extract(YEAR_MONTH from endtime)-extract(YEAR_MONTH from starttime)+1 inter from act
) a,nums
where nums.id<=a.inter
) b group by ts;

ITPub博客

查看每个月举办的活动总数

最新文章