CREATE PROCEDURE `flow_report4`() begin DROP TABLE IF EXISTS test4; CREATE TABLE test4(id2 INT not null AUTO_INCREMENT primary key) ( SELECT a.*,TIMEDIFF(a.`DELIVER_TIME`,b.`DELIVER_TIME`) AS deal_time,test2 AS avg_time FROM test2 a LEFT JOIN test2 b ON a.`id2`=b.`id2`+1 AND a.`RUN_ID`=b.`RUN_ID` INNER JOIN ( SELECT USER_ID,round(SUM(aa)/(3600*(SELECT COUNT(*) FROM test2 WHERE `USER_ID`=a1.`USER_ID`)),2) AS test2, (SELECT COUNT(*) FROM test2 WHERE `USER_ID`=a1.`USER_ID`) FROM ( SELECT a.*,b.`DELIVER_TIME` AS bsj,TIMEDIFF(a.`DELIVER_TIME`,b.`DELIVER_TIME`) AS aa FROM test2 a LEFT JOIN test2 b ON a.`id2`=b.id2+1 AND a.`RUN_ID`=b.`RUN_ID` ) a1 GROUP BY USER_ID ) c1 ON a.`USER_ID`=c1.`USER_ID` ); end 这就说那句存储过程,好像时间设到2011-09-01 到2011-11-30就会出错,2011-10-01 到2011-11-30就不会出错
begin
DROP TABLE IF EXISTS test4;
CREATE TABLE test4(id2 INT not null AUTO_INCREMENT primary key) (
SELECT a.*,TIMEDIFF(a.`DELIVER_TIME`,b.`DELIVER_TIME`) AS deal_time,test2 AS avg_time
FROM test2 a LEFT JOIN test2 b ON a.`id2`=b.`id2`+1 AND a.`RUN_ID`=b.`RUN_ID`
INNER JOIN
(
SELECT USER_ID,round(SUM(aa)/(3600*(SELECT COUNT(*) FROM test2 WHERE `USER_ID`=a1.`USER_ID`)),2) AS test2,
(SELECT COUNT(*) FROM test2 WHERE `USER_ID`=a1.`USER_ID`)
FROM (
SELECT a.*,b.`DELIVER_TIME` AS bsj,TIMEDIFF(a.`DELIVER_TIME`,b.`DELIVER_TIME`) AS aa
FROM test2 a LEFT JOIN test2 b ON a.`id2`=b.id2+1 AND a.`RUN_ID`=b.`RUN_ID`
) a1 GROUP BY USER_ID
) c1
ON a.`USER_ID`=c1.`USER_ID`
);
end 这就说那句存储过程,好像时间设到2011-09-01 到2011-11-30就会出错,2011-10-01 到2011-11-30就不会出错
可能性有2个:
1.你检查一下你对应的字段数据类型,是不是匹配了,timediff连接的数据可能有问题,数据形式是不是对了
2.数据是正确的,那就是确实不能转换了,数据类型都是有前后界限的,你可以试着换个方法转换一下一点建议,不知道对不对,期待楼主揭晓