存储过程:
BEGIN
declare countj int;
SELECT COUNT(serverid) INTO countj FROM `count_serverinfo` WHERE gameid = temgameid ORDER BY serverid,days;
BEGIN
INSERT into daydate(game1,serverorder,serverid,days)
SELECT game,serverorder,serverid,MAX(IF(days=temdays,amountcount,0.00)) AS days FROM count_serverinfo GROUP BY serverid ORDER BY serverid DESC;
END;
ENDphp文件中的代码:
$day = $db->query("SELECT days FROM `count_serverinfo` WHERE gameid = '1' GROUP BY days asc ");
foreach($day as $key=>$days){
$db->getAll("set @temgameid='1',@temdays='".$days['days']."'");
$sql="call dier(@temgameid,@temdays)";
$db->getAll($sql);
}这个值temdays我在php文件中从一张表里边查询出来了,然后遍历,比如说这个变量有10个值是在存储过程中循环了10次,但是插入到daydate表中的值全都是第一天的数据,为什么啊
BEGIN
declare countj int;
SELECT COUNT(serverid) INTO countj FROM `count_serverinfo` WHERE gameid = temgameid ORDER BY serverid,days;
BEGIN
INSERT into daydate(game1,serverorder,serverid,days)
SELECT game,serverorder,serverid,MAX(IF(days=temdays,amountcount,0.00)) AS days FROM count_serverinfo GROUP BY serverid ORDER BY serverid DESC;
END;
ENDphp文件中的代码:
$day = $db->query("SELECT days FROM `count_serverinfo` WHERE gameid = '1' GROUP BY days asc ");
foreach($day as $key=>$days){
$db->getAll("set @temgameid='1',@temdays='".$days['days']."'");
$sql="call dier(@temgameid,@temdays)";
$db->getAll($sql);
}这个值temdays我在php文件中从一张表里边查询出来了,然后遍历,比如说这个变量有10个值是在存储过程中循环了10次,但是插入到daydate表中的值全都是第一天的数据,为什么啊
SELECT 变量名
贴建表及插入记录的SQL
5-6条记录即可
BEGIN
declare countj int;
SELECT COUNT(serverid) INTO countj FROM `count_serverinfo` WHERE gameid = temgameid ORDER BY serverid,days;
BEGIN
INSERT into daydate(game1,serverorder,serverid,days)
SELECT game,serverorder,serverid,MAX(IF(days=temdays,amountcount,0.00)) AS days FROM count_serverinfo GROUP BY serverid ORDER BY serverid DESC;
END;
END建表:CREATE TABLE `daydate` (
`game1` varchar(20) DEFAULT NULL,
`serverorder` int(11) DEFAULT NULL,
`serverid` int(11) DEFAULT NULL,
`days` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk;表中的数据:INSERT INTO `daydate` VALUES ('帝国文明', '9', '18', '2940');
INSERT INTO `daydate` VALUES ('帝国文明', '8', '17', '3525');
INSERT INTO `daydate` VALUES ('帝国文明', '7', '10', '2945');
INSERT INTO `daydate` VALUES ('帝国文明', '6', '9', '2555');
INSERT INTO `daydate` VALUES ('帝国文明', '5', '7', '1620');
INSERT INTO `daydate` VALUES ('帝国文明', '4', '5', '1030');
INSERT INTO `daydate` VALUES ('帝国文明', '3', '3', '320');
INSERT INTO `daydate` VALUES ('帝国文明', '2', '2', '530');
INSERT INTO `daydate` VALUES ('帝国文明', '1', '1', '155');
$sql="call dier('1','".$days['days']."')";
$db->getAll($sql);
}
Date: 2012-7-12 16:14:11
SQLID: OUT or INOUT argument 1 for routine my6543db.dier is not a variable or NEW pseudo-variable in BEFORE trigger
Error: call dier('1','1')
CREATE PROCEDURE
在MYSQL中测试,结果如何
DROP PROCEDURE IF EXISTS `diera`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `dier`(
INOUT temdays INT,
IN temgameid INT,
)
BEGIN
DECLARE countj INT;
SELECT COUNT(DISTINCT(days)) INTO countj FROM `count_serverinfo` WHERE gameid = '",gameid,"';
BEGIN
INSERT into daydate(i,game1,serverorder,serverid,day1)
SELECT game,serverorder,serverid,MAX(IF(days='",days,"',amountcount,0.00)) AS '",days,"' FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC;
END;
END $$
DELIMITER ;
call dier(@temgameid,@temdays) 我这样在mysql中运行,查出来的数据正确啊,我把sql输出,我限制了只循环两次,你看他就出来了两条sql语句,而且这个temdays值也是变化的,
但是在存储过程中显示的虽然是两次查询结果,但都是第一天的数据
set @temgameid='1',@temdays='1'
call dier(@temgameid,@temdays)
set @temgameid='1',@temdays='2'
call dier(@temgameid,@temdays)
SELECT game,serverorder,serverid,MAX(IF(days='",days,"',amountcount,0.00)) AS '",days,"' FROM count_serverinfo GROUP BY serverid ORDER BY
这句上,上个帖子已经说过,要用字符串累加生成SQL语句,再执行的方法SET @asql='SELECT game,serverorder,serverid,';
SET @asql=CONCAT(@asql,'MAX(IF(days=\'',days,'\',amountcount,0.00)) AS `',days,'`');....
prepare stml from @asql;
execute stml;MAX(IF(days='",days,"',amountcount,0.00))
看看days中的值,你是否要计算比如days=‘01’ OR 02的值MAX(IF(days='01',amountcount,0.00))
这2句是否有区别
INOUT temdays INT,
IN temgameid INT,
SELECT COUNT(DISTINCT(days)) INTO countj FROM `count_serverinfo` WHERE gameid = '",gameid,"';
这句中gameid=temgameid
SELECT game,serverorder,serverid,MAX(IF(days='",days,"',amountcount,0.00)) AS '",days,"' FROM count_serverinfo GROUP BY serverid ORDER BY
这句中:days='",days,"', 后边的days=temdays
SET @asql='INSERT into daydate(game1,serverorder,serverid,days)';
SET @asql=CONCAT(@asql,'SELECT game,serverorder,serverid,');
SET @asql=CONCAT(@asql,'MAX(IF(days=\'',temdays,'\',amountcount,0.00)) AS `',temdays,'`FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC');
declare countj int;
SELECT COUNT(serverid) INTO countj FROM `count_serverinfo` WHERE gameid = temgameid ORDER BY serverid,days;
BEGIN
SET @asql='INSERT into daydate(game1,serverorder,serverid,days)';
SET @asql=CONCAT(@asql,'SELECT game,serverorder,serverid,');
SET @asql=CONCAT(@asql,'MAX(IF(days=\'',temdays,'\',amountcount,0.00)) AS `',temdays,'`FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC');
prepare stml from @asql;
execute stml;
deallocate prepare stml;
END;
END
DROP PROCEDURE IF EXISTS `diera`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `dier`(
INOUT temdays INT,
IN temgameid INT,
)
BEGIN
DECLARE countj INT;
SELECT COUNT(DISTINCT(days)) INTO countj FROM `count_serverinfo` WHERE gameid = '",temgameid,"';
BEGIN
INSERT into daydate(i,game1,serverorder,serverid,day1)
SELECT game,serverorder,serverid,MAX(IF(days='",temdays ,"',amountcount,0.00)) AS '",days,"' FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC;
END;
END $$
DELIMITER ;
DROP PROCEDURE IF EXISTS `diera`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `diera`(
INOUT temdays INT,
IN temgameid INT,
)
BEGIN
DECLARE countj INT;
SELECT COUNT(DISTINCT(days)) INTO countj FROM `count_serverinfo` WHERE gameid = ",temgameid,"';
BEGIN
INSERT into daydate(i,game1,serverorder,serverid,day1)
SELECT game,serverorder,serverid,MAX(IF(days='",temdays ,"',amountcount,0.00)) AS '",days,"' FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC;
END;
END $$
DELIMITER ;
SELECT @ASQL;
贴结果
mySQL Query Error
Date: 2012-7-13 09:44:22
SQLID: Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.
Error: set @temgameid='1',@temdays='2'