CREATE DEFINER=`root`@`localhost` PROCEDURE `diyi`(
IN days int,
IN day1 int,
INOUT game VARCHAR(50) character set gb2312,
IN serverorder int,
IN serverid int,
IN game1 varchar(20) character set gb2312
)BEGIN
DECLARE countj INT; -- 临时存储重复UID总数
DECLARE i INT; -- 临时循环变量存储
DECLARE days INT;
DECLARE game varchar(50) character set gbk;
DECLARE game1 varchar(50) character set gbk;
DECLARE serverorder INT;
DECLARE serverid INT;
DECLARE day1 INT;
SET i=1;
SELECT count(DISTINCT(days)) INTO countj FROM `count_serverinfo` WHERE gameid = '",game,"';
SELECT days INTO days FROM `count_serverinfo` WHERE gameid = '",game,"' GROUP BY days asc ;
WHILE i <= countj DO
BEGIN
SELECT game,serverorder,serverid,MAX(IF(days='",days,"',amountcount,0.00)) AS '",days,"' INTO game1,serverorder,serverid,day1 FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC;
END;
SET i = i +1;
END WHILE;
END
IN days int,
IN day1 int,
INOUT game VARCHAR(50) character set gb2312,
IN serverorder int,
IN serverid int,
IN game1 varchar(20) character set gb2312
)BEGIN
DECLARE countj INT; -- 临时存储重复UID总数
DECLARE i INT; -- 临时循环变量存储
DECLARE days INT;
DECLARE game varchar(50) character set gbk;
DECLARE game1 varchar(50) character set gbk;
DECLARE serverorder INT;
DECLARE serverid INT;
DECLARE day1 INT;
SET i=1;
SELECT count(DISTINCT(days)) INTO countj FROM `count_serverinfo` WHERE gameid = '",game,"';
SELECT days INTO days FROM `count_serverinfo` WHERE gameid = '",game,"' GROUP BY days asc ;
WHILE i <= countj DO
BEGIN
SELECT game,serverorder,serverid,MAX(IF(days='",days,"',amountcount,0.00)) AS '",days,"' INTO game1,serverorder,serverid,day1 FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC;
END;
SET i = i +1;
END WHILE;
END
CREATE DEFINER=`root`@`localhost` PROCEDURE `diyi`(
IN days INT,
IN day1 INT,
INOUT game VARCHAR(50) CHARACTER SET gb2312,
IN serverorder INT,
IN serverid INT,
IN game1 VARCHAR(20) CHARACTER SET gb2312
)BEGIN
DECLARE countj INT; -- 临时存储重复UID总数
DECLARE i INT; -- 临时循环变量存储
DECLARE days INT;
DECLARE game VARCHAR(50) CHARACTER SET gbk;
DECLARE game1 VARCHAR(50) CHARACTER SET gbk;
DECLARE serverorder INT;
DECLARE serverid INT;
DECLARE day1 INT;
SET i=1;
SELECT COUNT(DISTINCT(days)) INTO countj FROM `count_serverinfo` WHERE gameid = '",game,"';
SELECT days INTO days FROM `count_serverinfo` WHERE gameid = '",game,"' GROUP BY days ASC ;
WHILE i <= countj DO
BEGIN
SELECT game,serverorder,serverid,MAX(IF(days='",days,"',amountcount,0.00)) AS '",days,"' INTO game1,serverorder,serverid,day1 FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC;
END;
SET i = i +1;
END WHILE;
END$$
DELIMITER ;
或者直接在命令行工具中使用。
SELECT game,serverorder,serverid,MAX(IF(days='",days,"',amountcount,0.00)) AS '",days,"' INTO game1,serverorder,serverid,day1 FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC;
然后我在php文件中这样写:
$sql="call diyi('@game','@days','@serverorder','@serverid','@day1')";
$query = $db->query($sql);
$result = $db->query('select @game,@days,@serverorder,@serverid,@day1');然后报的错:
Error:Incorrect number of arguments for PROCEDURE my6543db.dier; expected 6, got 5
Errno:1318
SQL::call dier('@game','@days','@serverorder','@serverid','@day1') 怎么还不对啊
IN days INT,
IN day1 INT,
INOUT game VARCHAR(50) CHARACTER SET gb2312,
IN serverorder INT,
IN serverid INT,
IN game1 VARCHAR(20) CHARACTER SET gb2312
Error:OUT or INOUT argument 3 for routine my6543db.dier is not a variable or NEW pseudo-variable in BEFORE trigger
Errno:1414
真蛋疼,这玩意我都不知道如何下手修改他,我看人家写的获取方法是:
call Sf_StudentInfo( '048 ', 'F20050510160659 ', '2 ',@a);
select @a;
最后一个参数必须是一个变量。 他的怎么是固定的值
call Sf_StudentInfo( '048 ', 'F20050510160659 ', '2 ',@a);
select @a;
这是在MYSQL中的
call dier( '2 ', '2 ', '1','2 ','2 ',@game);
select @game;但是报这个错误
OUT or INOUT argument 2 for routine my6543db.dier is not a variable or NEW pseudo-variable in BEFORE trigger 我百度一下,全是英文,然后一句一句的翻译,最后,没有明白,
SELECT COUNT(DISTINCT(days)) INTO countj FROM `count_serverinfo` WHERE gameid = '",game,"';->
SELECT COUNT(DISTINCT(days)) INTO countj FROM `count_serverinfo` WHERE gameid =concat('\'',game,'\'');
SELECT days INTO days FROM `count_serverinfo` WHERE gameid = '",game,"' GROUP BY days ASC ;->
SELECT days INTO days FROM `count_serverinfo` WHERE gameid =concat('\'',game,'\''); GROUP BY days ASC ;这句什么意思
SELECT game,serverorder,serverid,MAX(IF(days='",days,"',amountcount,0.00)) AS '",days,"'
假设为days字符型
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;
CREATE DEFINER=`root`@`localhost` PROCEDURE `diyi`(
IN days INT,
IN day1 INT,
INOUT game VARCHAR(50) CHARACTER SET gb2312,
IN serverorder INT,
IN serverid INT,
IN game1 VARCHAR(20) CHARACTER SET gb2312
)BEGIN
DECLARE countj INT; -- 临时存储重复UID总数
DECLARE i INT; -- 临时循环变量存储
DECLARE days INT;
DECLARE game VARCHAR(50) CHARACTER SET gbk;
DECLARE game1 VARCHAR(50) CHARACTER SET gbk;
DECLARE serverorder INT;
DECLARE serverid INT;
DECLARE day1 INT;
SET i=1;
SELECT COUNT(DISTINCT(days)) INTO countj FROM `count_serverinfo` WHERE gameid = '",game,"';
SELECT days INTO days FROM `count_serverinfo` WHERE gameid = '",game,"' GROUP BY days ASC ;
WHILE i <= countj DO
BEGIN
SELECT game,serverorder,serverid,MAX(IF(days='",days,"',amountcount,0.00)) AS '",days,"' INTO game1,serverorder,serverid,day1 FROM count_serverinfo GROUP BY serverid ORDER BY serverorder DESC;
END;
SET i = i +1;
END WHILE;
END$$
DELIMITER ;这个里边的值我是不是还需要返回一下,才能在php文件中获取到,我该怎么返回这里边的值
2、如果你要返回转置后的表,生成1个物理表,
create table tt as select ....,访问此表即可
DROP PROCEDURE IF EXISTS `dier`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `dier`(
IN days INT,
IN day1 INT,
IN game VARCHAR(50) CHARACTER SET gb2312,
IN serverorder INT,
in serverid INT,
IN game1 VARCHAR(20) CHARACTER SET gb2312
)
BEGIN
DECLARE countj INT;
DECLARE i INT;
DECLARE days INT;
DECLARE game VARCHAR(50) CHARACTER SET gbk;
DECLARE game1 VARCHAR(50) CHARACTER SET gbk;
DECLARE serverorder INT;
DECLARE serverid INT;
DECLARE day1 INT;
CREATE TEMPORARY TABLE daydate(
id int AUTO_INCREMENT PRIMARY KEY,
game1 VARCHAR(15),
serverorder int(11),
serverid int(11),
day1 int(11)
);
SELECT COUNT(DISTINCT(days)) INTO countj FROM `count_serverinfo` WHERE gameid = '",game,"';
SELECT days INTO days FROM `count_serverinfo` WHERE gameid = '",game,"' GROUP BY days ASC ;
WHILE i <= countj DO
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;
SET i = i +1;
END WHILE;
SELECT id,game1,serverorder,serverid,day1 from daydate ORDER BY id;
END $$;
DELIMITER ;
在MYSQL环境中运行一下,看看结果
神仙道 2 32 0.00
MAX(IF(days='",days,"',amountcount,0.00))
看看days中的值,你是否要计算比如days=‘01’ OR 02的值MAX(IF(days='01',amountcount,0.00))
这2句是否有区别
MAX(IF(days='",days,"',amountcount,0.00))
值应该永远为0,
因为'",days,"'是1个字符串