按我理解应该是每月初生成一条新的记录插入到月表去吧?delimiter //
create procedure Calcu_MonTotal()
begin
DECLARE v_date varchar(16);
DECLARE v_total int(11);
SELECT DATE_FORMAT( DATE_ADD( NOW( ) , INTERVAL '-1' MONTH ),'%Y-%m') into v_date,sum(daytotal) into v_total
FROM ribao
WHERE ddate> DATE_FORMAT( DATE_ADD( NOW( ) , INTERVAL '-1' MONTH ),'%Y-%m')
and ddate < DATE_FORMAT(NOW(),'%Y-%m');
insert into yuetotal (ddate,yuetotal) values (v_date,v_total);
end
create procedure Calcu_MonTotal()
begin
DECLARE v_date varchar(16);
DECLARE v_total int(11);
SELECT DATE_FORMAT( DATE_ADD( NOW( ) , INTERVAL '-1' MONTH ),'%Y-%m') into v_date,sum(daytotal) into v_total
FROM ribao
WHERE ddate> DATE_FORMAT( DATE_ADD( NOW( ) , INTERVAL '-1' MONTH ),'%Y-%m')
and ddate < DATE_FORMAT(NOW(),'%Y-%m');
insert into yuetotal (ddate,yuetotal) values (v_date,v_total);
end
解决方案 »
- MySql数据库中主键的索引是什么样的?
- mysql 求一个简单查询语句
- 怎么解决Could not start the service MySQL
- 今天下午面试20几个题就知道3、4个其他的都不知道
- 有没有mysql的存储过程实例,长点的,完整的,项目中用到的
- 寻求关于transactions和triggers方面的资料!!!
- 在MySql中定义一对一关系
- 求大神帮忙啊 , mysql 创建数据库后,使用符号链接将数据库移动到另外的磁盘后,访问不了表项
- ibdata1太大了,怎么在不删数据或者少删数据情况下使mqsql运行
- mysql忘了创建database了 脚本执行后建的表去哪了?
- MYSQL 多表查询
- 很急!!!关于 将excel表格部分列数据导入到sql指定表的问题
create procedure Calcu_MonTotal()
begin
DECLARE v_date varchar(16);
DECLARE v_total int(11);
SELECT DATE_FORMAT( DATE_ADD( NOW( ) , INTERVAL '-1' MONTH ),'%Y-%m'),sum(daytotal) into v_date,v_total
FROM ribao
WHERE ddate> DATE_FORMAT( DATE_ADD( NOW( ) , INTERVAL '-1' MONTH ),'%Y-%m')
and ddate < DATE_FORMAT(NOW(),'%Y-%m');
insert into yuetotal (ddate,yuetotal) values (v_date,v_total);
end
ribao(id, tagname, ddate, daytotal)和yuebao(id, tagname, ddate, yuetotal),
这样是否存在一个问题,ribao表每个位号(tagname)都有多条记录,如2014-04-01到2014-04-30就30条记录,我想把每个位号4月01日到30日的daytotal字段相加后,存储在yuebao表中,yuebao的ddate字段格式为2014-04,这样的话,sql语句该怎么写啊?
create procedure Calcu_MonTotal()
begin
DECLARE v_date varchar(16);
DECLARE v_total int;
DECLARE v_tagname varchar(32);
SELECT DATE_FORMAT( DATE_ADD( NOW( ) , INTERVAL '-1' MONTH ),'%Y-%m') into v_date;
DECLARE cursor c_Fetch
For select tagname,sum(daytotal)
FROM ribao
WHERE ddate> DATE_FORMAT( DATE_ADD( NOW( ) , INTERVAL '-1' MONTH ),'%Y-%m')
and ddate < DATE_FORMAT(NOW(),'%Y-%m') group by tagname;
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
OPEN c_Fetch;
REPEAT
FETCH c_Fetch INTO v_tagname,v_total;
begin
insert into yuetotal (tagname,ddate,yuetotal) values (v_tagname,v_date,v_total);
end;
UNTIL stopFlag = 1
END REPEAT;
CLOSE c_Fetch;
end
create procedure Calcu_MonTotal()
begin
DECLARE v_date varchar(16);
DECLARE v_total int;
DECLARE v_tagname varchar(32);
SELECT DATE_FORMAT( DATE_ADD( NOW( ) , INTERVAL '-1' MONTH ),'%Y-%m') into v_date;
DECLARE c_Fetch CURSOR
FOR SELECT tagname,sum(daytotal)
FROM ribao
WHERE ddate> DATE_FORMAT( DATE_ADD( NOW( ) , INTERVAL '-1' MONTH ),'%Y-%m')
and ddate < DATE_FORMAT(NOW(),'%Y-%m') group by tagname;
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
OPEN c_Fetch;
REPEAT
FETCH c_Fetch INTO v_tagname,v_total;
begin
insert into yuetotal (tagname,ddate,yuetotal) values (v_tagname,v_date,v_total);
end;
UNTIL stopFlag = 1
END REPEAT;
CLOSE c_Fetch;
end
调试报错信息如下:
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 'declare c_Fetch cursor for select tagname,sum(daytotal) from ribao whe' at line 8
select语句能成功执行吗?
SELECT tagname,sum(daytotal)
FROM ribao
WHERE ddate> DATE_FORMAT( DATE_ADD( NOW( ) , INTERVAL '-1' MONTH ),'%Y-%m')
and ddate < DATE_FORMAT(NOW(),'%Y-%m') group by tagname;
ddate tagname yuetaotal
2014-03 ti1011 ?
2014-04 ti1011 ?
2014-03 ti1021 ?
2014-04 ti1021 ?
FROM ribao
WHERE ddate> DATE_FORMAT( DATE_ADD( NOW( ) , INTERVAL '-1' MONTH ),'%Y-%m')
and ddate < DATE_FORMAT(NOW(),'%Y-%m') group by tagname,dt;看看这个能不能正确得到结果
现在存储过能正确运行,但最后一条记录在yubao表多插入一次,游标循环还是有点小问题
.......
declare stopFlag int default 0;
.......
open c_Fetch;
repeat
fetch c_Fetch into v_date,v_tagname,v_total;
if stopFlag = 0 then
insert into yuebao(ddate,tagname,device,yuetotal)
values (v_date,v_tagname,v_total);
end if;
until stopFlag = 1 end repeat;
close c_Fetch;