wwwwb，WWWWA你们看看这个情况

yanfangphp 2012-07-12 03:12:34
存储过程：
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

php文件中的代码：
$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表中的值全都是第一天的数据，为什么啊

...全文
635 27 打赏 收藏 转发到动态 举报
写回复
用AI写文章
27 条回复
切换为时间正序
请发表友善的回复…
发表回复
yanfangphp 2012-07-13
  • 打赏
  • 举报
 回复
问题解决了，我把循环过程放到存储过程里边了，就没有问题了
WWWWA 2012-07-13
  • 打赏
  • 举报
 回复
[Quote=引用 23 楼 wwwwa 的回复:]

SET @asql=CONCAT(@asql,'MAX(IF(days=\'',temdays,'\',amountcount,0.00)) AS `',temdays,'`FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC');
SELECT @ASQL;
贴结果
[/Quote]
直接在MYSQL环境中运行，确定是否是SP的问题
yanfangphp 2012-07-13
  • 打赏
  • 举报
 回复
[Quote=引用 22 楼 的回复:]

SELECT game,serverorder,serverid,MAX(IF(days=temdays,amountcount,0.00)) FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC;
[/Quote]不行啊，插入表中的数据还是不循环，只插入了第一天的数据
yanfangphp 2012-07-13
  • 打赏
  • 举报
 回复
[Quote=引用 23 楼 的回复:]

SET @asql=CONCAT(@asql,'MAX(IF(days=\'',temdays,'\',amountcount,0.00)) AS `',temdays,'`FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC');
SELECT @ASQL;
贴结果
[/Quote]报错：
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'
WWWWA 2012-07-13
  • 打赏
  • 举报
 回复
SET @asql=CONCAT(@asql,'MAX(IF(days=\'',temdays,'\',amountcount,0.00)) AS `',temdays,'`FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC');
SELECT @ASQL;
贴结果
ACMAIN_CHM 2012-07-12
  • 打赏
  • 举报
 回复
SELECT game,serverorder,serverid,MAX(IF(days=temdays,amountcount,0.00)) FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC;
yanfangphp 2012-07-12
  • 打赏
  • 举报
 回复
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 ;
yanfangphp 2012-07-12
  • 打赏
  • 举报
 回复
DELIMITER $$
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 ;
ACMAIN_CHM 2012-07-12
  • 打赏
  • 举报
 回复
为什么不肯贴你完整的语句啊？你的 create procedure 在哪儿？
yanfangphp 2012-07-12
  • 打赏
  • 举报
 回复
BEGIN
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
yanfangphp 2012-07-12
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 的回复:]

set @temgameid='1',@temdays='1';
call dier(@temgameid,@temdays) 我这样在mysql中运行，查出来的数据正确啊，

我把sql输出，我限制了只循环两次，你看他就出来了两条sql语句，而且这个temdays值也是变化的，
但是在存储过程中显示的虽然是两次查询结果，但都是第一天的数据
set @temgameid='1',@te……
[/Quote]我都这样写了，还是一个样子啊，真蛋疼啊
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');
ACMAIN_CHM 2012-07-12
  • 打赏
  • 举报
 回复
[Quote]不好意思，我这是又写了一次存储过程：sql语句中的两个变量忘记修改了，，[/Quote]直接贴出你的 create procedure 语句，否则别人根本不知道你的语句是什么。
wwwwb 2012-07-12
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 yanfangphp 的回复:]

按照你这样的写法语法不对啊，我换双引号也不行勒
[/Quote]
你的代码是什么？建议你看看MYSQL的HELP、及我的示例进行修改
yanfangphp 2012-07-12
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 的回复:]

引用 8 楼 的回复:

DELIMITER $$
DROP PROCEDURE IF EXISTS `diera`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `dier`(
INOUT temdays INT,
IN temgameid INT,
)
BEGIN
DECLARE countj INT;
SELECT COUN……
[/Quote]不好意思，我这是又写了一次存储过程：sql语句中的两个变量忘记修改了，，
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
yanfangphp 2012-07-12
  • 打赏
  • 举报
 回复
按照你这样的写法语法不对啊，我换双引号也不行勒
ACMAIN_CHM 2012-07-12
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 的回复:]

DELIMITER $$
DROP PROCEDURE IF EXISTS `diera`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `dier`(
INOUT temdays INT,
IN temgameid INT,
)
BEGIN
DECLARE countj INT;
SELECT COUNT(DISTINCT(……
[/Quote]你的存储过程中哪儿用到了这两个参数？
INOUT temdays INT,
IN temgameid INT,
wwwwb 2012-07-12
  • 打赏
  • 举报
 回复
我估计问题出在
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句是否有区别
yanfangphp 2012-07-12
  • 打赏
  • 举报
 回复
set @temgameid='1',@temdays='1';
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)
wwwwb 2012-07-12
  • 打赏
  • 举报
 回复
在MYSQL中调用SP，结果如何
yanfangphp 2012-07-12
  • 打赏
  • 举报
 回复
DELIMITER $$
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 ;
加载更多回复（7）
微信支付开发订单查询实例
因为某一方技术的原因，可能导致商户在预期时间内都收不到最终支付通知，此时商户可以通过该API来查询... “appid” : “wwwwb4f85f3a797777”,  “package” : “out_trade_no=11122&partner=1900090055&sign=4e8
mysql get seq no_mysql的存储怎么写？帮我看看我这个写的哪里有问题？谢谢
你的位置:问答吧-> MySQL-> 问题详情mysql的存储怎么写？帮我看看我这个写的哪里有问题？谢谢DELIMITER $$;DROP PROCEDURE IF EXISTS `ebill`.`SP_GETSEQNO`$$CREATE DEFINER=`AUTEK`@`%` PROCEDURE `SP_GE...
MySQL表名后接t_急急急，MySql中执行的所有脚本都提示Table"DataBase名.表名"doesn't Exist!...
作者: zuoxingyu 发布时间: 2010-09-29 在数据库test目录下是否有t1.frm 作者: wwwwb 发布时间: 2010-09-29 OK，3Q，问题已解决，因为是远程连接数据库，要把所有交互的表名改写成为`小写表名` 作者: mabing0411 ...
分组排序
统计上经常会遇到分组排序问题，凭直觉一句SQL应该可以搞掂，...很快CSDN的wwwwb大侠手拔刀相助：（ ACMAIN_CHM也提供了另一个参考链接，再次一并谢过两位！） select * from tt a where 5=总分) 刚看到这句SQ
mysqldump导出数据库视图_mysql中如何用mysqldump批量如何导出视图view啊？
你的位置:问答吧-> MySQL-> 问题详情mysql中如何用mysqldump批量如何导出视图view啊？...我有3个库act、fdp、vtd里面有80张左右的视图，我想搬到生产环境中，mysqldump能不能一次批量导出啊？作者: mchdba发...
MySQL

56,679

社区成员

56,709

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章