自己练手写了个存储过程,主要就是用到临时表和时间转换的一些函数知识。逻辑有点恶心,但能达到效果,一条跨几天也可以,代码如下:
drop PROCEDURE if EXISTS proc1;
create PROCEDURE proc1(in pid int,in startdate date,in enddate date) MODIFIES sql DATA
Home:
BEGIN
declare gt,it int;
declare stime,etime,temptime,preday datetime;
DECLARE done INT DEFAULT FALSE;
declare cur1 cursor for select starttime,endtime,
datediff(endtime,starttime) from pt where person_id=pid
and starttime>startdate and endtime<enddate
;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
if(startdate>enddate) then leave Home;
end if;
drop TEMPORARY table if EXISTS temptb;
create TEMPORARY table temptb(person_id int,rq date,xs int);
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO stime, etime, gt;
IF done THEN LEAVE read_loop;
END IF;
IF(gt=0) THEN
insert into temptb values
(pid,DATE_FORMAT(stime,'%Y-%m-%d'),TIMESTAMPDIFF(hour,stime,etime));
END IF;
IF(gt>0) THEN
set it=0;
while it<=gt DO
set temptime=date_add(stime,interval it+1 day);
set preday=date_add(stime,interval it day);
if it=0 THEN
insert into temptb values(pid,
DATE_FORMAT(stime,'%Y-%m-%d'),
TIMESTAMPDIFF(hour,stime,DATE_FORMAT(temptime,'%Y-%m-%d')));
elseif it=gt THEN
insert into temptb values(pid,
DATE_FORMAT(etime,'%Y-%m-%d'),
TIMESTAMPDIFF(hour,DATE_FORMAT(etime,'%Y-%m-%d'),etime));
ELSE
insert into temptb values(pid,
DATE_FORMAT(preday,'%Y-%m-%d'),24);
end if;
set it=it+1;
end while;
end if;
END LOOP;
CLOSE cur1;
select * from temptb;
end
调用 call proc1(2,'2018-06-01','2018-06-30') 参数含义:2表示person_id,后面两个表示要查询的起止日期。
执行结果如下,这里就不做group by和sum了: