-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$CREATE DEFINER=`root`@`%` PROCEDURE `RechargeLog_SumTime`(
)
BEGIN SET @startTime = "2011-12-06 00:00:00";
SET @endTime = "2011-12-06 23:00:00";
SET @count = 0;
SET @logDate = "";
SET @tmp = "";
SET @str = "";
WHILE @startTime < @endTime DO
SELECT
count(RechargeLogId),
DATE_FORMAT(@startTime, '%Y-%m-%d %H') LogDate
INTO @count, @logDate
FROM `DataAnalysis`.`RechargeLog`
WHERE LogDate
BETWEEN
@startTime
AND
DATE_ADD(@startTime, INTERVAL 1 DAY_HOUR);
IF @count IS NULL OR @count = "" THEN
SET @count = 0;
END IF;
SET @tmp = CONCAT("{"+@count + "," +@logDate+"}");
SET @str = CONCAT(@tmp + "/" + @str);
SET @startTime = DATE_ADD(@startTime, INTERVAL 1 DAY_HOUR);
END WHILE; SELECT @str;
END$$这是执行后返回结果:46262
但是我想要的是这样的结果:{count,time}/{count,time} ......
这样的一个长的字符串
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$CREATE DEFINER=`root`@`%` PROCEDURE `RechargeLog_SumTime`(
)
BEGIN SET @startTime = "2011-12-06 00:00:00";
SET @endTime = "2011-12-06 23:00:00";
SET @count = 0;
SET @logDate = "";
SET @tmp = "";
SET @str = "";
WHILE @startTime < @endTime DO
SELECT
count(RechargeLogId),
DATE_FORMAT(@startTime, '%Y-%m-%d %H') LogDate
INTO @count, @logDate
FROM `DataAnalysis`.`RechargeLog`
WHERE LogDate
BETWEEN
@startTime
AND
DATE_ADD(@startTime, INTERVAL 1 DAY_HOUR);
IF @count IS NULL OR @count = "" THEN
SET @count = 0;
END IF;
SET @tmp = CONCAT("{"+@count + "," +@logDate+"}");
SET @str = CONCAT(@tmp + "/" + @str);
SET @startTime = DATE_ADD(@startTime, INTERVAL 1 DAY_HOUR);
END WHILE; SELECT @str;
END$$这是执行后返回结果:46262
但是我想要的是这样的结果:{count,time}/{count,time} ......
这样的一个长的字符串
解决方案 »
- 怎样快速的在几张表中查找相同的数据
- 帮看一下怎么不好使呢?
- where 中有if吗
- mysql到底还是不是免费的?
- MySQL数据库表可以有多大?
- 请问各位大虾怎样在ems mysql manager 3 lite中对数据库进行备份?谢谢
- MYSQL 用ROOT登陆问题
- 【求教】mysql left join 优化问题
- 请大家帮我解决一下MySQL已经都设置成UTF8还是乱码的问题
- /etc/mysql/mysql.conf.d/mysqld.cnf和/etc/mysql/my.cnf的区别
- [求助]mysql如何通过事件对一个表中数据定时做记录
- mysql新手问题:You have an error in your SQL syntax
SET @tmp = CONCAT("{"+@count + "," +@logDate+"}");
SET @str = CONCAT(@tmp + "/" + @str);
->
SET @tmp = CONCAT("{",@count, "," ,@logDate,"}");
SET @str = CONCAT(@tmp , "/" , @str);
SET @tmp = CONCAT("{"+@count + "," +@logDate+"}");
SET @str = CONCAT(@tmp + "/" + @str);
select @str后返回的值确实是:{count,time}/{count,time}的结果 但是如果数据过多的话就返回一个数值,是不是@str不够装啊?
or
将@str中的内容插入1个表中保存
将@str中的内容插入1个表中保存,再SELECT 此表查看
...
SET @tmp = CONCAT("{",@count, "," ,@logDate,"}");
SET @str = CONCAT(@tmp , "/" , @str);
insert into lsb values(@str);
....
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$CREATE DEFINER=`root`@`%` PROCEDURE `RechargeLog_SumTime`(
)
BEGIN SET @startTime = "2011-12-06 00:00:00";
SET @endTime = "2011-12-06 23:00:00";
SET @count = 0;
SET @logDate = "";
SET @tmp = "";
SET @str = "";
WHILE @startTime < @endTime DO
SELECT
count(RechargeLogId),
DATE_FORMAT(@startTime, '%Y-%m-%d %H') LogDate
INTO @count, @logDate
FROM `DataAnalysis`.`RechargeLog`
WHERE LogDate
BETWEEN
@startTime
AND
DATE_ADD(@startTime, INTERVAL 1 DAY_HOUR);
IF @count IS NULL OR @count = "" THEN
SET @count = 0;
END IF;
SET @tmp = CONCAT("{"+@count + "," +@logDate+"}");
insert into test(test)value(@tmp);
SET @startTime = DATE_ADD(@startTime, INTERVAL 1 DAY_HOUR);
END WHILE;
END$$
表的内容。。
2011
2011
2015
2014
2011
2011
2011
2011
2011
2012
2012
2011
2011
2011
2011
2011
2011
2011
2011
2011
2011
2011
2011
晕了返回的结果乱了。。
SET @tmp = CONCAT("{",@count , "," ,@logDate,"}");
insert into test(test)value(@tmp);
insert into test(test)value(@tmp);