按日期的小时统计 每一天 个时间段内的访问量
procedure如下,本身没错 但是 call 就出错了;
delimiter $$
DROP PROCEDURE IF exists lzp_count_by_date;
create procedure lzp_count_by_date()
begin
declare i ,k int default 0;
declare table_name char(8);
SET table_name = date_format(DATE_ADD(curdate(), INTERVAL -1 DAY),"%Y%m%d");
while (i <24) do
SET @sqlcmd1=concat("select count(*) into " ,k," from lzp",table_name," where hour(a)= ",i);
set @sqlcmd2= concat("insert into lzp_c",table_name," values (" ,i,",",k,")");
PREPARE p1 FROM @sqlcmd1;
EXECUTE p1;
DEALLOCATE PREPARE p1;
PREPARE p2 FROM @sqlcmd2;
EXECUTE p2;
DEALLOCATE PREPARE p2;
set k=0;
end while;
end
$$
delimiter ;
错误信息:
mysql> call lzp_count_by_date();
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 '0 from lzp20121202 where hour(a)= 0' at line 1
mysql>
procedure如下,本身没错 但是 call 就出错了;
delimiter $$
DROP PROCEDURE IF exists lzp_count_by_date;
create procedure lzp_count_by_date()
begin
declare i ,k int default 0;
declare table_name char(8);
SET table_name = date_format(DATE_ADD(curdate(), INTERVAL -1 DAY),"%Y%m%d");
while (i <24) do
SET @sqlcmd1=concat("select count(*) into " ,k," from lzp",table_name," where hour(a)= ",i);
set @sqlcmd2= concat("insert into lzp_c",table_name," values (" ,i,",",k,")");
PREPARE p1 FROM @sqlcmd1;
EXECUTE p1;
DEALLOCATE PREPARE p1;
PREPARE p2 FROM @sqlcmd2;
EXECUTE p2;
DEALLOCATE PREPARE p2;
set k=0;
end while;
end
$$
delimiter ;
错误信息:
mysql> call lzp_count_by_date();
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 '0 from lzp20121202 where hour(a)= 0' at line 1
mysql>
解决方案 »
- navicat 备份生成 psc psb
- 高手前来看看,外网的服务器如何连接这个内网的mysql数据库呢?
- 求一个mysql批量插入数据的纯sql脚本
- mysql workbench无法连接mysql?
- 求关于删除MYSQL中的重复记录的SQL语句
- Mysql有没有办法在插入时获得当前自增Id的函数,我有两个列用到该id?
- 请教各位高手一个关于 日期 的问题!
- 用delete,如果匹配条件是对两个表,怎么办呢?(谢谢)
- mysql update 和硬盘关系
- 数据库设计中,会为每个注册用户都新建一张表吗
- [讨论]mysql innoDb数据库对事物的处理速度
- mysql 查询(想不出方法来,看看各位的SQL)
cishu int
);