关于数据库(MYSQL)时间段的拆分问题,怎么样编程实现? 本帖最后由 u010735215 于 2014-11-10 17:29:52 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 DELIMITER //create procedure test(in pi_begin_time datetime,in pi_end_time datetime)beginDECLARE l_start_dt datetime;DECLARE l_end_dt datetime;DECLARE l_date datetime;DECLARE l_str varchar(30);set l_start_dt = pi_begin_time;loop_label: LOOPset l_str = concat(date_format(l_start_dt,'%Y-%m-%d %H'),':','59');set l_end_dt = str_to_date(l_str,'%Y-%m-%d %H:%i');if l_end_dt>=pi_end_time thenselect l_start_dt,pi_end_time;leave loop_label;end if;select l_start_dt,l_end_dt;set l_start_dt = DATE_ADD(l_end_dt,INTERVAL 1 MINUTE);END LOOP;END;// DELIMITER //create procedure test(in pi_begin_time datetime,in pi_end_time datetime)beginDECLARE l_start_dt datetime;DECLARE l_end_dt datetime;DECLARE l_date datetime;DECLARE l_str varchar(30);set l_start_dt = pi_begin_time;loop_label: LOOPset l_str = concat(date_format(l_start_dt,'%Y-%m-%d %H'),':','59');set l_end_dt = str_to_date(l_str,'%Y-%m-%d %H:%i');if l_end_dt>=pi_end_time thenselect l_start_dt,pi_end_time;leave loop_label;end if;select l_start_dt,l_end_dt;set l_start_dt = DATE_ADD(l_end_dt,INTERVAL 1 MINUTE);END LOOP;END;//具体怎么执行呢?? 假设表名为test;DELIMITER $$create procedure test()BEGINDECLARE l_start_dt1 datetime;DECLARE l_end_dt1 datetime;DECLARE l_date datetime;DECLARE l_str varchar(30);DECLARE l_seq int;DECLARE l_start_dt datetime;DECLARE l_end_dt datetime;DECLARE done INT DEFAULT FALSE;DECLARE C1 CURSOR FOR SELECT SEQ,str_to_date(concat(begindate,' ',begintime),'%Y-%c-%e %k:%i') START_DT,str_to_date(concat(enddate,' ',endtime),'%Y-%c-%e %k:%i') END_DT from test;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN C1;l_loop: LOOPFETCH C1 into l_seq,l_start_dt,l_end_dt;IF done THEN LEAVE l_loop;END IF;set l_start_dt1 = l_start_dt;loop_label: LOOP set l_str = concat(date_format(l_start_dt1,'%Y-%m-%d %H'),':','59');set l_end_dt1 = str_to_date(l_str,'%Y-%m-%d %H:%i');if l_end_dt1>=l_end_dt thenselect l_seq,l_start_dt1,l_end_dt;leave loop_label;elseselect l_seq,l_start_dt1,l_end_dt1;end if;set l_start_dt1 = DATE_ADD(l_end_dt1,INTERVAL 1 MINUTE);END LOOP loop_label;END LOOP l_loop;CLOSE C1;END$$ DELIMITER ;--调用call test(); 是不是在MySQL直接运行就可以了?不好意思,我没有学过存储过程…… 是不是在MySQL直接运行就可以了?不好意思,我没有学过存储过程…… 是不是直接在MySQL运行就可以了?不好意思,我没有学过存储过程。使用起来还是迷糊……假设表名为test;DELIMITER $$create procedure test()BEGINDECLARE l_start_dt1 datetime;DECLARE l_end_dt1 datetime;DECLARE l_date datetime;DECLARE l_str varchar(30);DECLARE l_seq int;DECLARE l_start_dt datetime;DECLARE l_end_dt datetime;DECLARE done INT DEFAULT FALSE;DECLARE C1 CURSOR FOR SELECT SEQ,str_to_date(concat(begindate,' ',begintime),'%Y-%c-%e %k:%i') START_DT,str_to_date(concat(enddate,' ',endtime),'%Y-%c-%e %k:%i') END_DT from test;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN C1;l_loop: LOOPFETCH C1 into l_seq,l_start_dt,l_end_dt;IF done THEN LEAVE l_loop;END IF;set l_start_dt1 = l_start_dt;loop_label: LOOP set l_str = concat(date_format(l_start_dt1,'%Y-%m-%d %H'),':','59');set l_end_dt1 = str_to_date(l_str,'%Y-%m-%d %H:%i');if l_end_dt1>=l_end_dt thenselect l_seq,l_start_dt1,l_end_dt;leave loop_label;elseselect l_seq,l_start_dt1,l_end_dt1;end if;set l_start_dt1 = DATE_ADD(l_end_dt1,INTERVAL 1 MINUTE);END LOOP loop_label;END LOOP l_loop;CLOSE C1;END$$ DELIMITER ;--调用call test(); 对。我都已经帮你写了:--调用call test(); 对。我都已经帮你写了:--调用call test();运行的时候出现这样的情况?? 对。我都已经帮你写了:--调用call test();运行的时候出现这样的情况??看看 test表的SEQ列存在不?下面的定义游标的sql用到了SEQ列,DECLARE C1 CURSOR FOR SELECT SEQ,str_to_date(concat(begindate,' ',begintime),'%Y-%c-%e %k:%i') START_DT,str_to_date(concat(enddate,' ',endtime),'%Y-%c-%e %k:%i') END_DT from test; 的确应该修改一下下 没有学过这个 刚读懂一些些对。我都已经帮你写了:--调用call test();运行的时候出现这样的情况??看看 test表的SEQ列存在不?下面的定义游标的sql用到了SEQ列,DECLARE C1 CURSOR FOR SELECT SEQ,str_to_date(concat(begindate,' ',begintime),'%Y-%c-%e %k:%i') START_DT,str_to_date(concat(enddate,' ',endtime),'%Y-%c-%e %k:%i') END_DT from test; 问一个树形菜单的问题 嵌入式LINUX与windows共用数据库的问题 关于MYSQL中直接插入数据...插不进去! php5与mysql5.0数据写入中文乱码问题,在线等! 100分求PostgreSql数据库的下载地址! 一个sql查询的问题 如何将dump数据备份导入到mysql数据库中 MySQl有与程储过程触发器类似的功能吗? 还是菜鸟的关于多表选择的问题,大虾们帮忙阿 mysqldump 不影响线上使用 mysql大表拆分,求方法 mysql存储过程中发生异常,怎么将异常的详细信息打印出来呢?
create procedure test(in pi_begin_time datetime,in pi_end_time datetime)
begin
DECLARE l_start_dt datetime;
DECLARE l_end_dt datetime;
DECLARE l_date datetime;
DECLARE l_str varchar(30);set l_start_dt = pi_begin_time;
loop_label: LOOPset l_str = concat(date_format(l_start_dt,'%Y-%m-%d %H'),':','59');
set l_end_dt = str_to_date(l_str,'%Y-%m-%d %H:%i');
if l_end_dt>=pi_end_time then
select l_start_dt,pi_end_time;
leave loop_label;
end if;
select l_start_dt,l_end_dt;
set l_start_dt = DATE_ADD(l_end_dt,INTERVAL 1 MINUTE);
END LOOP;
END;
//
create procedure test(in pi_begin_time datetime,in pi_end_time datetime)
begin
DECLARE l_start_dt datetime;
DECLARE l_end_dt datetime;
DECLARE l_date datetime;
DECLARE l_str varchar(30);set l_start_dt = pi_begin_time;
loop_label: LOOPset l_str = concat(date_format(l_start_dt,'%Y-%m-%d %H'),':','59');
set l_end_dt = str_to_date(l_str,'%Y-%m-%d %H:%i');
if l_end_dt>=pi_end_time then
select l_start_dt,pi_end_time;
leave loop_label;
end if;
select l_start_dt,l_end_dt;
set l_start_dt = DATE_ADD(l_end_dt,INTERVAL 1 MINUTE);
END LOOP;
END;
//
具体怎么执行呢??
DELIMITER $$
create procedure test()
BEGIN
DECLARE l_start_dt1 datetime;
DECLARE l_end_dt1 datetime;
DECLARE l_date datetime;
DECLARE l_str varchar(30);
DECLARE l_seq int;
DECLARE l_start_dt datetime;
DECLARE l_end_dt datetime;
DECLARE done INT DEFAULT FALSE;
DECLARE C1 CURSOR FOR SELECT SEQ,str_to_date(concat(begindate,' ',begintime),'%Y-%c-%e %k:%i') START_DT,str_to_date(concat(enddate,' ',endtime),'%Y-%c-%e %k:%i') END_DT from test;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN C1;
l_loop: LOOP
FETCH C1 into l_seq,l_start_dt,l_end_dt;
IF done THEN
LEAVE l_loop;
END IF;
set l_start_dt1 = l_start_dt;loop_label: LOOP
set l_str = concat(date_format(l_start_dt1,'%Y-%m-%d %H'),':','59');
set l_end_dt1 = str_to_date(l_str,'%Y-%m-%d %H:%i');
if l_end_dt1>=l_end_dt then
select l_seq,l_start_dt1,l_end_dt;
leave loop_label;
else
select l_seq,l_start_dt1,l_end_dt1;
end if;
set l_start_dt1 = DATE_ADD(l_end_dt1,INTERVAL 1 MINUTE);
END LOOP loop_label;
END LOOP l_loop;
CLOSE C1;
END$$ DELIMITER ;
--调用
call test();
是不是直接在MySQL运行就可以了?不好意思,我没有学过存储过程。
使用起来还是迷糊……假设表名为test;
DELIMITER $$
create procedure test()
BEGIN
DECLARE l_start_dt1 datetime;
DECLARE l_end_dt1 datetime;
DECLARE l_date datetime;
DECLARE l_str varchar(30);
DECLARE l_seq int;
DECLARE l_start_dt datetime;
DECLARE l_end_dt datetime;
DECLARE done INT DEFAULT FALSE;
DECLARE C1 CURSOR FOR SELECT SEQ,str_to_date(concat(begindate,' ',begintime),'%Y-%c-%e %k:%i') START_DT,str_to_date(concat(enddate,' ',endtime),'%Y-%c-%e %k:%i') END_DT from test;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN C1;
l_loop: LOOP
FETCH C1 into l_seq,l_start_dt,l_end_dt;
IF done THEN
LEAVE l_loop;
END IF;
set l_start_dt1 = l_start_dt;loop_label: LOOP
set l_str = concat(date_format(l_start_dt1,'%Y-%m-%d %H'),':','59');
set l_end_dt1 = str_to_date(l_str,'%Y-%m-%d %H:%i');
if l_end_dt1>=l_end_dt then
select l_seq,l_start_dt1,l_end_dt;
leave loop_label;
else
select l_seq,l_start_dt1,l_end_dt1;
end if;
set l_start_dt1 = DATE_ADD(l_end_dt1,INTERVAL 1 MINUTE);
END LOOP loop_label;
END LOOP l_loop;
CLOSE C1;
END$$ DELIMITER ;
--调用
call test();
我都已经帮你写了:--调用
call test();
我都已经帮你写了:--调用
call test();
运行的时候出现这样的情况??
我都已经帮你写了:--调用
call test();
运行的时候出现这样的情况??
看看 test表的SEQ列存在不?下面的定义游标的sql用到了SEQ列,
DECLARE C1 CURSOR FOR SELECT SEQ,str_to_date(concat(begindate,' ',begintime),'%Y-%c-%e %k:%i') START_DT,str_to_date(concat(enddate,' ',endtime),'%Y-%c-%e %k:%i') END_DT from test;
的确应该修改一下下 没有学过这个 刚读懂一些些
对。
我都已经帮你写了:--调用
call test();
运行的时候出现这样的情况??
看看 test表的SEQ列存在不?下面的定义游标的sql用到了SEQ列,
DECLARE C1 CURSOR FOR SELECT SEQ,str_to_date(concat(begindate,' ',begintime),'%Y-%c-%e %k:%i') START_DT,str_to_date(concat(enddate,' ',endtime),'%Y-%c-%e %k:%i') END_DT from test;