if !exists(select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='sgsi' and `TABLE_NAME`='statistics_iae_report') then
CREATE TABLE statistics_iae_report
(
idx INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (idx),
alla INT NOT NULL default 0,
areaId INT NOT NULL default 0,
gGMoney INT NOT NULL default 0,
cGMoney INT NOT NULL default 0,
gGGold INT NOT NULL default 0,
cGGold INT NOT NULL default 0,
gPst INT NOT NULL default 0,
cPst INT NOT NULL default 0,
gMilitary INT NOT NULL default 0,
cMilitary INT NOT NULL default 0,
gArenaPoint INT NOT NULL default 0,
cArenaPoint INT NOT NULL default 0,
createTime DATETIME
);
end if;
SET maxAreaId = 2;
SET currentAreaId = 1;loop2:WHILE currentAreaId<=maxAreaId DOSET @AID = currentAreaId;(SELECT sum(gmoney) INTO gGMoney FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(ggold) INTO gGGold FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(military) INTO gMilitary FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(pst) INTO gPst FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(arenaPoint) INTO gArenaPoint FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);(SELECT sum(gmoney) INTO cGMoney FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(ggold) INTO cGGold FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(military) INTO cPst FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(pst) INTO cMilitary FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(arenaPoint) INTO cArenaPoint FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);INSERT INTO statistics_iae_report(alla,areaId,cGMoney,gGMoney,gGGold,cGGold,gPst,cPst,gMilitary,cMilitary,gArenaPoint,cArenaPoint,createTime)SELECT count('alla'),areaId,gGMoney,
0,
gGGold,
0,
gPst,
0,
gMilitary,
0,
gArenaPoint,
0,
cGMoney,
0,
cGGold,
0,
cPst,
0,
cMilitary,
0,
cArenaPoint,
0,
now()
from log_consumption where createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID;SET currentAreaId=currentAreaId+1;END WHILE loop2;上面是存储过程中的代码,上面图片是我的统计表。麻烦大大有明白的帮忙解决下。新手在线等
CREATE TABLE statistics_iae_report
(
idx INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (idx),
alla INT NOT NULL default 0,
areaId INT NOT NULL default 0,
gGMoney INT NOT NULL default 0,
cGMoney INT NOT NULL default 0,
gGGold INT NOT NULL default 0,
cGGold INT NOT NULL default 0,
gPst INT NOT NULL default 0,
cPst INT NOT NULL default 0,
gMilitary INT NOT NULL default 0,
cMilitary INT NOT NULL default 0,
gArenaPoint INT NOT NULL default 0,
cArenaPoint INT NOT NULL default 0,
createTime DATETIME
);
end if;
SET maxAreaId = 2;
SET currentAreaId = 1;loop2:WHILE currentAreaId<=maxAreaId DOSET @AID = currentAreaId;(SELECT sum(gmoney) INTO gGMoney FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(ggold) INTO gGGold FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(military) INTO gMilitary FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(pst) INTO gPst FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(arenaPoint) INTO gArenaPoint FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);(SELECT sum(gmoney) INTO cGMoney FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(ggold) INTO cGGold FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(military) INTO cPst FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(pst) INTO cMilitary FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(arenaPoint) INTO cArenaPoint FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);INSERT INTO statistics_iae_report(alla,areaId,cGMoney,gGMoney,gGGold,cGGold,gPst,cPst,gMilitary,cMilitary,gArenaPoint,cArenaPoint,createTime)SELECT count('alla'),areaId,gGMoney,
0,
gGGold,
0,
gPst,
0,
gMilitary,
0,
gArenaPoint,
0,
cGMoney,
0,
cGGold,
0,
cPst,
0,
cMilitary,
0,
cArenaPoint,
0,
now()
from log_consumption where createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID;SET currentAreaId=currentAreaId+1;END WHILE loop2;上面是存储过程中的代码,上面图片是我的统计表。麻烦大大有明白的帮忙解决下。新手在线等
cGGold,0,cPst,0,cMilitary,0,cArenaPoint,0,now()
from log_consumption
where createTime>=current_date()
AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day)
AND areaId=@AID;
没这样写sql的啊 count不能这么出来的要么count去掉 要么所有列加sum或者count
呃,这是我的,其它的数据都是从别的表查出来的,alla,是统计这个表里面有多少条数据的。那应该是怎么写呢?如果去掉count的话,那不就是统计不出来了吗?
SELECT @NUM;
管理,这句话要插在哪里?INSERT INTO statistics_iae_report(alla,areaId,cGMoney,gGMoney,gGGold,cGGold,gPst,cPst,gMilitary,cMilitary,gArenaPoint,cArenaPoint,createTime)这句话的下面吗?
declare gGGold int(20);/**/
declare gPst int(20);/**/
declare gMilitary int(20);/**/
declare gArenaPoint int(20);/**/declare cGMoney int(20);/**/
declare cGGold int(20);/**/
declare cPst int(20);/**/
declare cMilitary int(20);/**/
declare cArenaPoint int(20);/**//*
*日游戏收支统计总表
*/
if !exists(select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='sgsi' and `TABLE_NAME`='statistics_iae_report') then
CREATE TABLE statistics_iae_report
(
idx INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (idx),
areaId INT NOT NULL default 0,
gGMoney INT NOT NULL default 0,
cGMoney INT NOT NULL default 0,
gGGold INT NOT NULL default 0,
cGGold INT NOT NULL default 0,
gPst INT NOT NULL default 0,
cPst INT NOT NULL default 0,
gMilitary INT NOT NULL default 0,
cMilitary INT NOT NULL default 0,
gArenaPoint INT NOT NULL default 0,
cArenaPoint INT NOT NULL default 0,
createTime DATETIME
);
end if;
SET maxAreaId = 2;
SET currentAreaId = 1;loop2:WHILE currentAreaId<=maxAreaId DOSET @AID = currentAreaId;(SELECT sum(gmoney) INTO gGMoney FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(ggold) INTO gGGold FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(military) INTO gMilitary FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(pst) INTO gPst FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(arenaPoint) INTO gArenaPoint FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);(SELECT sum(gmoney) INTO cGMoney FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(ggold) INTO cGGold FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(military) INTO cPst FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(pst) INTO cMilitary FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(arenaPoint) INTO cArenaPoint FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);INSERT INTO statistics_iae_report(alla,areaId,cGMoney,gGMoney,gGGold,cGGold,gPst,cPst,gMilitary,cMilitary,gArenaPoint,cArenaPoint,createTime)/**/
SELECT count(*) INTO @NUM FROM statistics_iae_report;
SELECT @NUM;
areaId,
0,
gGMoney,
0,
gGGold,
0,
gPst,
0,
gMilitary,
0,
gArenaPoint,
0,
cGMoney,
0,
cGGold,
0,
cPst,
0,
cMilitary,
0,
cArenaPoint,
0,
now()
from log_consumption where createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID;
SET currentAreaId=currentAreaId+1;END WHILE loop2;
declare gGMoney int(20);/**/
declare gGGold int(20);/**/
declare gPst int(20);/**/
declare gMilitary int(20);/**/
declare gArenaPoint int(20);/**/declare cGMoney int(20);/**/
declare cGGold int(20);/**/
declare cPst int(20);/**/
declare cMilitary int(20);/**/
declare cArenaPoint int(20);/**//*
*日游戏收支统计总表
*/
if !exists(select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='sgsi' and `TABLE_NAME`='statistics_iae_report') then
CREATE TABLE statistics_iae_report
(
idx INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (idx),
areaId INT NOT NULL default 0,
gGMoney INT NOT NULL default 0,
cGMoney INT NOT NULL default 0,
gGGold INT NOT NULL default 0,
cGGold INT NOT NULL default 0,
gPst INT NOT NULL default 0,
cPst INT NOT NULL default 0,
gMilitary INT NOT NULL default 0,
cMilitary INT NOT NULL default 0,
gArenaPoint INT NOT NULL default 0,
cArenaPoint INT NOT NULL default 0,
createTime DATETIME
);
end if;
SET maxAreaId = 2;
SET currentAreaId = 1;loop2:WHILE currentAreaId<=maxAreaId DOSET @AID = currentAreaId;(SELECT sum(gmoney) INTO gGMoney FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(ggold) INTO gGGold FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(military) INTO gMilitary FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(pst) INTO gPst FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(arenaPoint) INTO gArenaPoint FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);(SELECT sum(gmoney) INTO cGMoney FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(ggold) INTO cGGold FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(military) INTO cPst FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(pst) INTO cMilitary FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(arenaPoint) INTO cArenaPoint FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);INSERT INTO statistics_iae_report(alla,areaId,cGMoney,gGMoney,gGGold,cGGold,gPst,cPst,gMilitary,cMilitary,gArenaPoint,cArenaPoint,createTime)/**/
SELECT count(*) INTO @NUM FROM statistics_iae_report;
SELECT @NUM;
areaId,
0,
gGMoney,
0,
gGGold,
0,
gPst,
0,
gMilitary,
0,
gArenaPoint,
0,
cGMoney,
0,
cGGold,
0,
cPst,
0,
cMilitary,
0,
cArenaPoint,
0,
now()
from log_consumption where createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID;
SET currentAreaId=currentAreaId+1;END WHILE loop2;
只要SELECT count(*) INTO @NUM FROM statistics_iae_report;SELECT @NUM;
其余去掉
还是报错。保存都保存不了的
*
*日游戏收支统计总表
*/
if !exists(select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='sgsi' and `TABLE_NAME`='statistics_iae_report') then
CREATE TABLE statistics_iae_report
(
idx INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (idx),
alla INT NOT NULL default 0,
areaId INT NOT NULL default 0,
gGMoney INT NOT NULL default 0,
cGMoney INT NOT NULL default 0,
gGGold INT NOT NULL default 0,
cGGold INT NOT NULL default 0,
gPst INT NOT NULL default 0,
cPst INT NOT NULL default 0,
gMilitary INT NOT NULL default 0,
cMilitary INT NOT NULL default 0,
gArenaPoint INT NOT NULL default 0,
cArenaPoint INT NOT NULL default 0,
createTime DATETIME
);
end if;
SET maxAreaId = 2;
SET currentAreaId = 1;loop2:WHILE currentAreaId<=maxAreaId DOSET @AID = currentAreaId;(SELECT sum(gmoney) INTO gGMoney FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(ggold) INTO gGGold FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(military) INTO gMilitary FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(pst) INTO gPst FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(arenaPoint) INTO gArenaPoint FROM log_gainings WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);(SELECT sum(gmoney) INTO cGMoney FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(ggold) INTO cGGold FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(military) INTO cPst FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(pst) INTO cMilitary FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);
(SELECT sum(arenaPoint) INTO cArenaPoint FROM log_consumption WHERE createTime>=current_date() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 day) AND areaId=@AID);INSERT INTO statistics_iae_report(alla,areaId,cGMoney,gGMoney,gGGold,cGGold,gPst,cPst,gMilitary,cMilitary,gArenaPoint,cArenaPoint,createTime)/**/
SELECT count(*) INTO alla FROM statistics_iae_report;
SELECT alla;
END WHILE loop2;
DELIMITER $$
CREATE PROCEDURE ff()
BEGIN
IF !EXISTS(SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`='sgsi' AND `TABLE_NAME`='statistics_iae_report') THENCREATE TABLE statistics_iae_report
(
idx INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (idx),
alla INT NOT NULL DEFAULT 0,
areaId INT NOT NULL DEFAULT 0,
gGMoney INT NOT NULL DEFAULT 0,
cGMoney INT NOT NULL DEFAULT 0,
gGGold INT NOT NULL DEFAULT 0,
cGGold INT NOT NULL DEFAULT 0,
gPst INT NOT NULL DEFAULT 0,
cPst INT NOT NULL DEFAULT 0,
gMilitary INT NOT NULL DEFAULT 0,
cMilitary INT NOT NULL DEFAULT 0,
gArenaPoint INT NOT NULL DEFAULT 0,
cArenaPoint INT NOT NULL DEFAULT 0,
createTime DATETIME
);
END IF;
SET @maxAreaId = 2;
SET @currentAreaId = 1;
loop2:WHILE currentAreaId<=maxAreaId DO
SET @AID = currentAreaId;
(SELECT SUM(gmoney) INTO gGMoney FROM log_gainings WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(ggold) INTO gGGold FROM log_gainings WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(military) INTO gMilitary FROM log_gainings WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(pst) INTO gPst FROM log_gainings WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(arenaPoint) INTO gArenaPoint FROM log_gainings WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(gmoney) INTO cGMoney FROM log_consumption WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(ggold) INTO cGGold FROM log_consumption WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(military) INTO cPst FROM log_consumption WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(pst) INTO cMilitary FROM log_consumption WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(arenaPoint) INTO cArenaPoint FROM log_consumption WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
INSERT INTO statistics_iae_report(alla,areaId,cGMoney,gGMoney,gGGold,cGGold,gPst,cPst,gMilitary,cMilitary,gArenaPoint,cArenaPoint,createTime)
SELECT COUNT(*) INTO @alla FROM statistics_iae_report;
SELECT @alla;
END WHILE loop2;
END$$
DELIMITER ;
declare gGMoney int(20);/**/
declare gGGold int(20);/**/
declare gPst int(20);/**/
declare gMilitary int(20);/**/
declare gArenaPoint int(20);/**/declare cGMoney int(20);/**/
declare cGGold int(20);/**/
declare cPst int(20);/**/
declare cMilitary int(20);/**/
declare cArenaPoint int(20);/**/
CREATE PROCEDURE ff()
BEGIN
DECLARE gGMoney INT(20);/**/
DECLARE gGGold INT(20);/**/
DECLARE gPst INT(20);/**/
DECLARE gMilitary INT(20);/**/
DECLARE gArenaPoint INT(20);/**/
DECLARE maxAreaId INT;
DECLARE cGMoney INT(20);/**/
DECLARE cGGold INT(20);/**/
DECLARE cPst INT(20);/**/
DECLARE cMilitary INT(20);/**/
DECLARE cArenaPoint INT(20);/**/
DECLARE currentAreaId INT;
IF !EXISTS(SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`='sgsi' AND `TABLE_NAME`='statistics_iae_report') THEN CREATE TABLE statistics_iae_report
(
idx INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (idx),
alla INT NOT NULL DEFAULT 0,
areaId INT NOT NULL DEFAULT 0,
gGMoney INT NOT NULL DEFAULT 0,
cGMoney INT NOT NULL DEFAULT 0,
gGGold INT NOT NULL DEFAULT 0,
cGGold INT NOT NULL DEFAULT 0,
gPst INT NOT NULL DEFAULT 0,
cPst INT NOT NULL DEFAULT 0,
gMilitary INT NOT NULL DEFAULT 0,
cMilitary INT NOT NULL DEFAULT 0,
gArenaPoint INT NOT NULL DEFAULT 0,
cArenaPoint INT NOT NULL DEFAULT 0,
createTime DATETIME
);
END IF;
SET maxAreaId = 2;
SET currentAreaId = 1;
loop2:WHILE currentAreaId<=maxAreaId DO
SET @AID = currentAreaId;
(SELECT SUM(gmoney) INTO gGMoney FROM log_gainings WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(ggold) INTO gGGold FROM log_gainings WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(military) INTO gMilitary FROM log_gainings WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(pst) INTO gPst FROM log_gainings WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(arenaPoint) INTO gArenaPoint FROM log_gainings WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(gmoney) INTO cGMoney FROM log_consumption WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(ggold) INTO cGGold FROM log_consumption WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(military) INTO cPst FROM log_consumption WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(pst) INTO cMilitary FROM log_consumption WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
(SELECT SUM(arenaPoint) INTO cArenaPoint FROM log_consumption WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID);
INSERT INTO statistics_iae_report(alla,areaId,cGMoney,gGMoney,gGGold,cGGold,gPst,cPst,gMilitary,cMilitary,gArenaPoint,cArenaPoint,createTime)
VALUES(
(SELECT COUNT(*) FROM log_consumption WHERE createTime>=CURRENT_DATE() AND createTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY) AND areaId=@AID),
areaId, gGMoney, gGGold, gPst, gMilitary, gArenaPoint, cGMoney, cGGold, cPst, cMilitary, cArenaPoint,NOW());
END WHILE loop2;
END;$$
DELIMITER ;
逻辑自行检查