这是存储过程结构:
DELIMITER $$DROP PROCEDURE IF EXISTS `jira20`.`pdm_c_total_number`$$CREATE DEFINER=`jira20`@`%` PROCEDURE `pdm_c_total_number`(p_time_interval SMALLINT UNSIGNED)
BEGIN
DECLARE v_begin DATETIME ;
DECLARE v_end DATETIME ;
SET v_begin = '2010-09-10 10:00:00';
SET v_end = '2010-09-10 17:00:00';
TRUNCATE TABLE dm_c_total_number;
WHILE v_begin <= v_end DO
INSERT INTO dm_c_total_number (
c_date,
c_begin_time,
c_end_time,
c_total_number
)
SELECT DATE(v_begin),
TIME(v_begin),
TIME(DATE_ADD(v_begin,INTERVAL p_time_interval HOUR)),
COUNT(*)
FROM v_jiraissue
WHERE DATE(created)='2010-09-10'
AND created BETWEEN TIME(v_begin) AND TIME(DATE_ADD(v_begin,INTERVAL p_time_interval HOUR));
SET v_begin = DATE_ADD(v_begin,INTERVAL p_time_interval HOUR);
END WHILE;
END$$DELIMITER ;但是结果却变成这样了:
dm_c_id c_date c_begin_time c_end_time c_total_number
1 2010-09-10 00:00:00 2010-00-00 00:00:00 2011-00-00 00:00:00 0
2 2010-09-10 00:00:00 2011-00-00 00:00:00 2012-00-00 00:00:00 0
3 2010-09-10 00:00:00 2012-00-00 00:00:00 2013-00-00 00:00:00 0
4 2010-09-10 00:00:00 2013-00-00 00:00:00 2014-00-00 00:00:00 0
5 2010-09-10 00:00:00 2014-00-00 00:00:00 2015-00-00 00:00:00 0
6 2010-09-10 00:00:00 2015-00-00 00:00:00 2016-00-00 00:00:00 0
7 2010-09-10 00:00:00 2016-00-00 00:00:00 2017-00-00 00:00:00 0
8 2010-09-10 00:00:00 2017-00-00 00:00:00 2018-00-00 00:00:00 0请指教是怎么回事。
下面是我这个表的结构:
DROP TABLE IF EXISTS dm_c_total_number;
CREATE TABLE dm_c_total_number (
dm_c_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
c_date DATETIME,
c_begin_time DATETIME,
c_end_time DATETIME,
c_total_number INT,
PRIMARY KEY (dm_c_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8; 谢谢。
DELIMITER $$DROP PROCEDURE IF EXISTS `jira20`.`pdm_c_total_number`$$CREATE DEFINER=`jira20`@`%` PROCEDURE `pdm_c_total_number`(p_time_interval SMALLINT UNSIGNED)
BEGIN
DECLARE v_begin DATETIME ;
DECLARE v_end DATETIME ;
SET v_begin = '2010-09-10 10:00:00';
SET v_end = '2010-09-10 17:00:00';
TRUNCATE TABLE dm_c_total_number;
WHILE v_begin <= v_end DO
INSERT INTO dm_c_total_number (
c_date,
c_begin_time,
c_end_time,
c_total_number
)
SELECT DATE(v_begin),
TIME(v_begin),
TIME(DATE_ADD(v_begin,INTERVAL p_time_interval HOUR)),
COUNT(*)
FROM v_jiraissue
WHERE DATE(created)='2010-09-10'
AND created BETWEEN TIME(v_begin) AND TIME(DATE_ADD(v_begin,INTERVAL p_time_interval HOUR));
SET v_begin = DATE_ADD(v_begin,INTERVAL p_time_interval HOUR);
END WHILE;
END$$DELIMITER ;但是结果却变成这样了:
dm_c_id c_date c_begin_time c_end_time c_total_number
1 2010-09-10 00:00:00 2010-00-00 00:00:00 2011-00-00 00:00:00 0
2 2010-09-10 00:00:00 2011-00-00 00:00:00 2012-00-00 00:00:00 0
3 2010-09-10 00:00:00 2012-00-00 00:00:00 2013-00-00 00:00:00 0
4 2010-09-10 00:00:00 2013-00-00 00:00:00 2014-00-00 00:00:00 0
5 2010-09-10 00:00:00 2014-00-00 00:00:00 2015-00-00 00:00:00 0
6 2010-09-10 00:00:00 2015-00-00 00:00:00 2016-00-00 00:00:00 0
7 2010-09-10 00:00:00 2016-00-00 00:00:00 2017-00-00 00:00:00 0
8 2010-09-10 00:00:00 2017-00-00 00:00:00 2018-00-00 00:00:00 0请指教是怎么回事。
下面是我这个表的结构:
DROP TABLE IF EXISTS dm_c_total_number;
CREATE TABLE dm_c_total_number (
dm_c_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
c_date DATETIME,
c_begin_time DATETIME,
c_end_time DATETIME,
c_total_number INT,
PRIMARY KEY (dm_c_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8; 谢谢。
解决方案 »
- 关于存储过程返回值的问题
- Excel中vba代码用odbc连接mysql数据库
- 如何在UPDATE语句中使用SELECT的结果?
- grall all 给 192.168.10.%赋予最大权限给某个库 但是更新失败
- mysql5.0用命令行查询正常,但用sql就乱码,已经加了&useUnicode=true&characterEncoding=GBK
- mysql 备份与恢复 问题。急急急急急 加急。100分来啊
- 求存储过程
- 远程连接mysql 无法连接
- 如何维持自增列
- PowerDesigner 修改表名,更新到数据库失败
- insert语句询问
- JProgressBar 进度显示问题
`issueid` int(11) default NULL,
`created` datetime default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into `u_jiraissue`(`issueid`,`created`) values (39735,'2010-09-10 10:31:23'),(39736,'2010-09-10 10:32:54'),(39737,'2010-09-10 10:34:45'),(39738,'2010-09-10 10:46:00'),(39739,'2010-09-10 10:58:23'),(39740,'2010-09-10 11:10:08'),(39741,'2010-09-10 11:15:40'),(39742,'2010-09-10 11:18:31'),(39743,'2010-09-10 14:19:01'),(39744,'2010-09-10 14:36:16'),(39745,'2010-09-10 15:09:36'),(39746,'2010-09-10 15:18:20'),(39747,'2010-09-10 15:23:07'),(39748,'2010-09-10 15:27:03'),(39749,'2010-09-10 15:29:29'),(39750,'2010-09-10 15:33:49'),(39751,'2010-09-10 15:37:20'),(39752,'2010-09-10 15:41:54'),(39753,'2010-09-10 15:49:02'),(39754,'2010-09-10 15:58:40'),(39755,'2010-09-10 15:59:10'),(39756,'2010-09-10 15:59:19'),(39757,'2010-09-10 16:01:02'),(39758,'2010-09-10 16:02:05'),(39759,'2010-09-10 16:09:44'),(39760,'2010-09-10 16:13:51'),(39761,'2010-09-10 16:25:04'),(39762,'2010-09-10 16:37:28'),(39763,'2010-09-10 17:00:31'),(39764,'2010-09-10 17:04:06'),(39765,'2010-09-10 17:12:34'),(39766,'2010-09-10 17:22:39'),(39767,'2010-09-10 17:27:58'),(39768,'2010-09-10 17:35:10'),(39769,'2010-09-10 17:36:10'),(39770,'2010-09-10 17:39:03'),(39771,'2010-09-10 17:39:23'),(39772,'2010-09-10 17:44:38'),(39773,'2010-09-10 17:47:53'),(39774,'2010-09-10 17:51:01'),(39775,'2010-09-10 17:58:28'),(39776,'2010-09-10 18:05:57'),(39777,'2010-09-10 18:23:15'),(39778,'2010-09-10 18:49:04'),(39779,'2010-09-10 21:29:32'),(39780,'2010-09-10 21:31:19'),(39781,'2010-09-10 21:32:05'),(39782,'2010-09-10 21:32:59'),(39783,'2010-09-10 21:33:57'),(39785,'2010-09-10 21:40:55');
谢谢。
`issueid` int(11) default NULL,
`created` datetime default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into `v_jiraissue`(`issueid`,`created`) values (39735,'2010-09-10 10:31:23'),(39736,'2010-09-10 10:32:54'),(39737,'2010-09-10 10:34:45'),(39738,'2010-09-10 10:46:00'),(39739,'2010-09-10 10:58:23'),(39740,'2010-09-10 11:10:08'),(39741,'2010-09-10 11:15:40'),(39742,'2010-09-10 11:18:31'),(39743,'2010-09-10 14:19:01'),(39744,'2010-09-10 14:36:16'),(39745,'2010-09-10 15:09:36'),(39746,'2010-09-10 15:18:20'),(39747,'2010-09-10 15:23:07'),(39748,'2010-09-10 15:27:03'),(39749,'2010-09-10 15:29:29'),(39750,'2010-09-10 15:33:49'),(39751,'2010-09-10 15:37:20'),(39752,'2010-09-10 15:41:54'),(39753,'2010-09-10 15:49:02'),(39754,'2010-09-10 15:58:40'),(39755,'2010-09-10 15:59:10'),(39756,'2010-09-10 15:59:19'),(39757,'2010-09-10 16:01:02'),(39758,'2010-09-10 16:02:05'),(39759,'2010-09-10 16:09:44'),(39760,'2010-09-10 16:13:51'),(39761,'2010-09-10 16:25:04'),(39762,'2010-09-10 16:37:28'),(39763,'2010-09-10 17:00:31'),(39764,'2010-09-10 17:04:06'),(39765,'2010-09-10 17:12:34'),(39766,'2010-09-10 17:22:39'),(39767,'2010-09-10 17:27:58'),(39768,'2010-09-10 17:35:10'),(39769,'2010-09-10 17:36:10'),(39770,'2010-09-10 17:39:03'),(39771,'2010-09-10 17:39:23'),(39772,'2010-09-10 17:44:38'),(39773,'2010-09-10 17:47:53'),(39774,'2010-09-10 17:51:01'),(39775,'2010-09-10 17:58:28'),(39776,'2010-09-10 18:05:57'),(39777,'2010-09-10 18:23:15'),(39778,'2010-09-10 18:49:04'),(39779,'2010-09-10 21:29:32'),(39780,'2010-09-10 21:31:19'),(39781,'2010-09-10 21:32:05'),(39782,'2010-09-10 21:32:59'),(39783,'2010-09-10 21:33:57'),(39785,'2010-09-10 21:40:55');
DELIMITER $$DROP PROCEDURE IF EXISTS `jira20`.`pdm_c_total_number`$$CREATE DEFINER=`jira20`@`%` PROCEDURE `pdm_c_total_number`(p_time_interval SMALLINT UNSIGNED)
BEGIN
DECLARE v_begin DATETIME ;
DECLARE v_end DATETIME ;
SET v_begin = '2010-09-10 10:00:00';
SET v_end = '2010-09-10 17:00:00';
TRUNCATE TABLE dm_c_total_number;
WHILE v_begin <= v_end DO
INSERT INTO dm_c_total_number (
c_date,
c_begin_time,
c_end_time,
c_total_number
)
SELECT DATE(v_begin),
TIME(v_begin),
TIME(DATE_ADD(v_begin,INTERVAL p_time_interval HOUR)),
COUNT(*)
FROM v_jiraissue
WHERE DATE(created)='2010-09-10'
AND TIME(created) BETWEEN TIME(v_begin) AND TIME(DATE_ADD(v_begin,INTERVAL p_time_interval HOUR));
SET v_begin = DATE_ADD(v_begin,INTERVAL p_time_interval HOUR);
END WHILE;
END$$DELIMITER ;现在结果成这样了:dm_c_id c_date c_begin_time c_end_time c_total_number
1 2010-09-10 00:00:00 2010-00-00 00:00:00 2011-00-00 00:00:00 5
2 2010-09-10 00:00:00 2011-00-00 00:00:00 2012-00-00 00:00:00 3
3 2010-09-10 00:00:00 2012-00-00 00:00:00 2013-00-00 00:00:00 0
4 2010-09-10 00:00:00 2013-00-00 00:00:00 2014-00-00 00:00:00 0
5 2010-09-10 00:00:00 2014-00-00 00:00:00 2015-00-00 00:00:00 2
6 2010-09-10 00:00:00 2015-00-00 00:00:00 2016-00-00 00:00:00 12
7 2010-09-10 00:00:00 2016-00-00 00:00:00 2017-00-00 00:00:00 6
8 2010-09-10 00:00:00 2017-00-00 00:00:00 2018-00-00 00:00:00 13
日期和时间还是显示错误。
我要的是这样的结果:
dm_c_id c_date c_begin_time c_end_time c_total_number
1 2010-09-10 10:00:00 11:00:00 5
2 2010-09-10 11:00:00 12:00:00 3
3 2010-09-10 12:00:00 13:00:00 0
4 2010-09-10 13:00:00 14:00:00 0
5 2010-09-10 14:00:00 15:00:00 2
6 2010-09-10 15:00:00 16:00:00 12
7 2010-09-10 16:00:00 17:00:00 6
8 2010-09-10 17:00:00 18:00:00 13
dm_c_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
c_date DATETIME,
c_begin_time TIME,
c_end_time TIME,
c_total_number INT,
PRIMARY KEY (dm_c_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
将DATETIME->TIME