SET @EE='';
SELECT @EE:=CONCAT(@EE,'sum(IF(p_name=\'',p_name,'\'',',amount,0)) AS `',p_name,'`,') FROM (SELECT DISTINCT p_name FROM history_number where pdate='2010-01-25' order by p_orderNo) A;
SET @QQ=CONCAT('SELECT ifnull(u_name,\'total\'),',@EE,'sum(amount) AS TOTAL FROM history_number where pdate=\'2010-01-25\' GROUP BY u_name WITH ROLLUP');PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
上面代码只能写在存储过程吗,能不能用jdbc写。如果只能写在存储过程,请问怎么返回结果集???
SELECT @EE:=CONCAT(@EE,'sum(IF(p_name=\'',p_name,'\'',',amount,0)) AS `',p_name,'`,') FROM (SELECT DISTINCT p_name FROM history_number where pdate='2010-01-25' order by p_orderNo) A;
SET @QQ=CONCAT('SELECT ifnull(u_name,\'total\'),',@EE,'sum(amount) AS TOTAL FROM history_number where pdate=\'2010-01-25\' GROUP BY u_name WITH ROLLUP');PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
上面代码只能写在存储过程吗,能不能用jdbc写。如果只能写在存储过程,请问怎么返回结果集???
解决方案 »
- 求助 请问如何将FastDB里的数据导入到MySQL中
- 求一条mysql sql语句。。。
- 求助!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!简单问题,我是菜鸟
- 质疑有设么方法可以让sqlserver 或者 oracle的db数据 导入到mysql
- 一个mysql建树的问题
- 紧急求教高手表损坏的一些学问?????
- mysql5.0.10-0在redhat as 3的安装启动不了的问题
- PGSql中如何设定修改用户密码?在PGADMIN中修改无效
- 請問在Linux下copy出來的mySQL文檔可不可以直接在Window2000下使用
- 关于Mysql WorkBench首页
- MYSQL触发器的问题,请高手解答
- mysql 存储过程中 如何用变量表示 表名
在语言环境中累加生成SQL语句,再将此字符串发回MYSQL执行,其实是一致的,
你可以生成临时表 OR 在语言环境中打开记录集取结果
DROP PROCEDURE IF EXISTS `newspaper`.`getList`$$
CREATE PROCEDURE `newspaper`.`getList` ()
BEGIN
SET @EE='';
SELECT @EE:=CONCAT(@EE,'sum(IF(p_name=\'',p_name,'\'',',amount,0)) AS `',p_name,'`,') FROM (SELECT DISTINCT p_name FROM history_number where pdate='2010-01-25' order by p_orderNo) A;
SET @QQ=CONCAT('SELECT ifnull(u_name,\'total\'),',@EE,'sum(amount) AS TOTAL FROM history_number where pdate=\'2010-01-25\' GROUP BY u_name WITH ROLLUP');PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
END$$DELIMITER ;$$
要怎么改。
不一定要写在存储过程中。
如果是存储过程,你可以直接向执行 select 语句一样的方法 call getList() 得到返回结果集。
call getList()得到的只是@ee的结果,并不能得到EXECUTE stmt2的结果
sql = "SET @EE='';";
pstmt = DB.prepareStmt(conn, sql);
pstmt.execute();
sql="SELECT @EE:=CONCAT(@EE,'sum(IF(p_name=\'',p_name,'\'',',amount,0)) AS `',p_name,'`,') FROM (SELECT DISTINCT p_name FROM history_number where pdate='2010-01-25' order by p_orderNo) A;";
pstmt = DB.prepareStmt(conn, sql);
pstmt.execute();
sql="SET @QQ=CONCAT('SELECT ifnull(u_name,\'total\'),',@EE,'sum(amount) AS TOTAL FROM history_number where pdate=\'2010-01-25\' GROUP BY u_name WITH ROLLUP');";
pstmt = DB.prepareStmt(conn, sql);
pstmt.execute();
sql="PREPARE stmt2 FROM @QQ;";
pstmt = DB.prepareStmt(conn, sql);
pstmt.execute();sql=" EXECUTE stmt2;";
pstmt = DB.prepareStmt(conn, sql);
pstmt.execute();用jdbc是这样写吗,执行时出错
DELIMITER $$;DROP PROCEDURE IF EXISTS `newspaper`.`getList`$$CREATE PROCEDURE `getList`(pdate varchar(20))
BEGIN
SET @EE='';
SELECT @EE:=CONCAT(@EE,'sum(IF(p_name=\'',p_name,'\'',',amount,0)) AS `',p_name,'`,') FROM (SELECT DISTINCT p_name FROM history_number where pdate=',pdate,' order by p_orderNo) A;
SET @QQ=CONCAT('SELECT ifnull(u_name,\'total\') as USER,',@EE,'sum(amount) AS TOTAL FROM history_number where pdate=',pdate,' GROUP BY u_name WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
END$$DELIMITER ;$$
BEGIN
SET @EE='';
SELECT @EE:=CONCAT(@EE,'sum(IF(p_name=\'',p_name,'\'',',amount,0)) AS `',p_name,'`,') FROM (SELECT DISTINCT p_name FROM history_number where pdate=\'',pdate,'\' order by p_orderNo) A;
SET @QQ=CONCAT('SELECT ifnull(u_name,\'total\') as USER,',@EE,'sum(amount) AS TOTAL FROM history_number where pdate=\'',pdate,'\' GROUP BY u_name WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
END$$ DELIMITER ;$$改成\'',pdate,'\'编译无法通过(影响 0 条记录)
(耗费 0 ms)错误代码: 1064
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 ''' order by p_orderNo) A;
SET @QQ=CONCAT('SELECT ifnull(u_name,\'total\') as US' at line 4
(耗费 0 ms)
BEGIN
SET @EE='';
SELECT @EE:=CONCAT(@EE,'sum(IF(p_name=\'',p_name,'\'',',amount,0)) AS `',p_name,'`,') FROM (SELECT DISTINCT p_name FROM history_number where pdate=v_pdate order by p_orderNo) A;
SET @QQ=CONCAT('SELECT ifnull(u_name,\'total\') as USER,',@EE,'sum(amount) AS TOTAL FROM history_number where pdate=\'',v_pdate,'\' GROUP BY u_name WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
END$$