34,587
社区成员
发帖
与我相关
我的任务
分享
drop table if exists t;
CREATE TABLE t (
id int NOT NULL AUTO_INCREMENT,
`d` datetime NOT NULL,
PRIMARY KEY (`id`)
);
INSERT t (d) values('2018-08-09 18:06');
INSERT t (d) values('2018-08-09 20:05');
INSERT t (d) values('2018-08-09 20:06');
INSERT t (d) values('2018-08-09 20:30');
INSERT t (d) values('2018-08-09 21:05');
INSERT t (d) values('2018-08-09 21:06');
drop table if exists my_dual;
create table my_dual(id int);
#2、创建存储过程(向表my_dual插入数据)
drop PROCEDURE if exists proc_insert_dual;
delimiter //
CREATE PROCEDURE proc_insert_dual(IN v_i INT)
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < v_i
DO
INSERT INTO my_dual VALUES (i);
SET i = i + 1;
END WHILE;
END;//
delimiter ;
#3、调用存储过程(向表my_dual插入5000数字,数字从0开始)
CALL proc_insert_dual(5000);
select
DATE_ADD(NOW(), INTERVAL (-m.id-1)*30 MINUTE) as beginT
,DATE_ADD(NOW(), INTERVAL m.id*(-30) MINUTE) as endT
,group_concat(t.d order by t.d desc separator ' | ') as myTimes
from my_dual as m left join t
on t.d BETWEEN DATE_ADD(NOW(), INTERVAL (-m.id-1)*30 MINUTE) and DATE_ADD(NOW(), INTERVAL m.id*(-30) MINUTE)
where t.d is not null
group by m.id
order by m.id asc;