我所需要的就是想知道从level1到level2平均需要多少时间,从level2到level3平均需要多少时间,依次类推,知道最高level所需结果如下
level days
1 1 -- level1到level2 平均需要1天
2 2 -- level2到level3平均需要2天
3 3 -- level3到level4平均需要3天
4 3 -- level3到level5平均需要4天
.
.
.
.
.谢谢,请指教,能否说下逻辑和思路。
我把表和数据重新整理了一下,这样应该比较清晰了:CREATE TABLE `dm_test` (
`pid` int(11) default NULL,
`level` int(11) default NULL,
`match_id` int(11) default NULL,
`result_time` datetime default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;数据:
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1012','1','96','2009-03-04 20:32:37');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1013','1','97','2009-03-04 20:39:55');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1013','1','98','2009-03-04 20:44:10');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1014','2','99','2009-03-06 20:49:14');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1014','3','100','2009-03-06 20:50:50');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1016','3','101','2009-03-06 21:06:39');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1017','3','102','2009-03-06 21:15:11');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1018','4','103','2009-03-07 21:18:07');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1019','4','104','2009-03-07 22:03:21');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1034','4','105','2009-03-08 22:04:21');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1056','5','106','2009-03-08 22:05:21');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1067','6','107','2009-03-08 22:12:23');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1088','7','108','2009-03-09 22:13:11');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1045','8','109','2009-03-10 22:13:51');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1023','9','110','2009-03-13 22:14:32');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1090','10','111','2009-03-15 22:15:07');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1123','11','112','2009-03-17 22:15:51');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1099','12','113','2009-03-15 22:36:01');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1890','12','114','2009-03-19 22:43:42');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('8909','12','115','2009-03-20 22:44:20');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('3789','11','116','2009-03-21 22:45:10');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1046','9','117','2009-03-22 22:47:27');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1050','7','118','2009-03-23 22:58:26');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1060','13','119','2009-03-23 22:59:05');
level days
1 1 -- level1到level2 平均需要1天
2 2 -- level2到level3平均需要2天
3 3 -- level3到level4平均需要3天
4 3 -- level3到level5平均需要4天
.
.
.
.
.谢谢,请指教,能否说下逻辑和思路。
我把表和数据重新整理了一下,这样应该比较清晰了:CREATE TABLE `dm_test` (
`pid` int(11) default NULL,
`level` int(11) default NULL,
`match_id` int(11) default NULL,
`result_time` datetime default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;数据:
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1012','1','96','2009-03-04 20:32:37');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1013','1','97','2009-03-04 20:39:55');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1013','1','98','2009-03-04 20:44:10');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1014','2','99','2009-03-06 20:49:14');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1014','3','100','2009-03-06 20:50:50');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1016','3','101','2009-03-06 21:06:39');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1017','3','102','2009-03-06 21:15:11');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1018','4','103','2009-03-07 21:18:07');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1019','4','104','2009-03-07 22:03:21');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1034','4','105','2009-03-08 22:04:21');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1056','5','106','2009-03-08 22:05:21');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1067','6','107','2009-03-08 22:12:23');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1088','7','108','2009-03-09 22:13:11');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1045','8','109','2009-03-10 22:13:51');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1023','9','110','2009-03-13 22:14:32');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1090','10','111','2009-03-15 22:15:07');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1123','11','112','2009-03-17 22:15:51');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1099','12','113','2009-03-15 22:36:01');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1890','12','114','2009-03-19 22:43:42');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('8909','12','115','2009-03-20 22:44:20');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('3789','11','116','2009-03-21 22:45:10');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1046','9','117','2009-03-22 22:47:27');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1050','7','118','2009-03-23 22:58:26');
insert into `dm_test` (`pid`, `level`, `match_id`, `result_time`) values('1060','13','119','2009-03-23 22:59:05');
mysql> CREATE TABLE `dm_test` (
-> `pid` int(11) default NULL,
-> `level` int(11) default NULL,
-> `match_id` int(11) default NULL,
-> `result_time` datetime default NULL
-> ) ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ',
`result_time` datetime default NULL
)' at line 4
+------+-------+----------+---------------------+
| pid | level | match_id | result_time |
+------+-------+----------+---------------------+
| 1012 | 1 | 96 | 2009-03-04 20:32:37 |
| 1013 | 1 | 97 | 2009-03-04 20:39:55 |
| 1013 | 1 | 98 | 2009-03-04 20:44:10 |
| 1014 | 2 | 99 | 2009-03-06 20:49:14 |
| 1014 | 3 | 100 | 2009-03-06 20:50:50 |
| 1016 | 3 | 101 | 2009-03-06 21:06:39 |
| 1017 | 3 | 102 | 2009-03-06 21:15:11 |
| 1018 | 4 | 103 | 2009-03-07 21:18:07 |
| 1019 | 4 | 104 | 2009-03-07 22:03:21 |
| 1034 | 4 | 105 | 2009-03-08 22:04:21 |
| 1056 | 5 | 106 | 2009-03-08 22:05:21 |
| 1067 | 6 | 107 | 2009-03-08 22:12:23 |
| 1088 | 7 | 108 | 2009-03-09 22:13:11 |
| 1045 | 8 | 109 | 2009-03-10 22:13:51 |
| 1023 | 9 | 110 | 2009-03-13 22:14:32 |
| 1090 | 10 | 111 | 2009-03-15 22:15:07 |
| 1123 | 11 | 112 | 2009-03-17 22:15:51 |
| 1099 | 12 | 113 | 2009-03-15 22:36:01 |
| 1890 | 12 | 114 | 2009-03-19 22:43:42 |
| 8909 | 12 | 115 | 2009-03-20 22:44:20 |
| 3789 | 11 | 116 | 2009-03-21 22:45:10 |
| 1046 | 9 | 117 | 2009-03-22 22:47:27 |
| 1050 | 7 | 118 | 2009-03-23 22:58:26 |
| 1060 | 13 | 119 | 2009-03-23 22:59:05 |
+------+-------+----------+---------------------+
24 rows in set (0.00 sec)mysql>
说明一下,这个数据是怎么算出来的?
1 1
2 2
3 3
4 3 为什么不是
1 2
2 4
3 2
4 5
Query : CALL aaa()
Error Code : 1172
Result consisted of more than one row
以下是我写得存储,请指教哪里错误。DELIMITER $$USE `osdktest2`$$DROP PROCEDURE IF EXISTS `aaa`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `aaa`()
BEGIN
DECLARE v_timeslot INT UNSIGNED;
DECLARE v_interval INT UNSIGNED;
DECLARE v_pid INT UNSIGNED;
DECLARE v_login_time DATETIME ;
DECLARE v_time DATETIME;
SET v_timeslot = 0;
SELECT pid,TIMESTAMPDIFF(MINUTE,DATE_FORMAT(login_time,'%Y-%m-%d %H:%i'),DATE_FORMAT(logout_time,'%Y-%m-%d %H:%i')) AS interval_time,
DATE_FORMAT(login_time,'%Y-%m-%d %H:%i') AS login_time
INTO v_pid,
v_interval,
v_login_time
FROM test;
WHILE v_timeslot <= v_interval DO
SELECT DATE_FORMAT(ADDDATE(DATE_FORMAT(login_time,'%Y-%m-%d %H:%i'),INTERVAL v_timeslot MINUTE),'%Y-%m-%d %H:%i') AS TIME
INTO v_time
FROM (
SELECT pid,TIMESTAMPDIFF(MINUTE,DATE_FORMAT(login_time,'%Y-%m-%d %H:%i'),DATE_FORMAT(logout_time,'%Y-%m-%d %H:%i')) AS interval_time,
DATE_FORMAT(login_time,'%Y-%m-%d %H:%i') AS login_time
FROM test ) a;
SET v_timeslot = v_timeslot + 1;
END WHILE;
END$$DELIMITER ;