按我理解应该是每月初生成一条新的记录插入到月表去吧?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
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;