最近把系统从mssql转到mysql上,原来有做统计的存储过程也要一并移过来,原来的存储过程是用行列转换,动态生成sql语句然后用exec执行的,换到mysql后改用EXECUTE执行,在mysql命令行中调用存储过程可以得到列表
+-------+--------------+-----------------+--------------+-----------+--------+
| se_no | max(se_code) | head | 娼滃湪瀹㈡埛 | 鏈€夋嫨 | 鍚
堣 |
+-------+--------------+-----------------+--------------+-----------+--------+
| 1 | NULL | 瓒呯骇绠$悊鍛? | 1 | 1 | 2
|
| NULL | NULL | 瓒呯骇绠$悊鍛? | 1 | 1 | 2
|
+-------+--------------+-----------------+--------------+-----------+--------+
(因为数据库是utf8的,所以列表是乱码),但用jdbc调用存储过程后好像没有执行execute语句,而是直接返回了动态生成的sql语句,就和在命令行中不执行最后两句
PREPARE stmt2 FROM @psql;
EXECUTE stmt2;
的效果一样,哪位对mysql比较熟悉的大哥能帮个忙啊 存储过程代码 CREATE PROCEDURE empCusState( arg_sqlAppend text )
BEGIN
SET @psql = 'select se_no,max(se_code),max(se_name) as head';
SELECT @psql:=CONCAT(@psql,
CASE WHEN typ_name IS NOT NULL
THEN (CONCAT(',sum(case typ_name when ''',typ_name,
''' then 1 else 0 end) as ',typ_name,' '))
ELSE (', sum(case when typ_name is null then 1 else 0 end) as 未选择')
END)
FROM
(select typ_name from cus_cor_cus
left join type_list on cor_typ_id=typ_id
where cor_isdelete='1' group by typ_name order by typ_name desc)
AS a;
SET @psql:=CONCAT(@psql,
',count(cor_code)as 合计 from cus_cor_cus
inner join sal_emp on cor_se_no=se_no
left join type_list on cor_typ_id=typ_id
where cor_isdelete=''1'' group by se_no with rollup');
PREPARE stmt2 FROM @psql;
EXECUTE stmt2;
END
+-------+--------------+-----------------+--------------+-----------+--------+
| se_no | max(se_code) | head | 娼滃湪瀹㈡埛 | 鏈€夋嫨 | 鍚
堣 |
+-------+--------------+-----------------+--------------+-----------+--------+
| 1 | NULL | 瓒呯骇绠$悊鍛? | 1 | 1 | 2
|
| NULL | NULL | 瓒呯骇绠$悊鍛? | 1 | 1 | 2
|
+-------+--------------+-----------------+--------------+-----------+--------+
(因为数据库是utf8的,所以列表是乱码),但用jdbc调用存储过程后好像没有执行execute语句,而是直接返回了动态生成的sql语句,就和在命令行中不执行最后两句
PREPARE stmt2 FROM @psql;
EXECUTE stmt2;
的效果一样,哪位对mysql比较熟悉的大哥能帮个忙啊 存储过程代码 CREATE PROCEDURE empCusState( arg_sqlAppend text )
BEGIN
SET @psql = 'select se_no,max(se_code),max(se_name) as head';
SELECT @psql:=CONCAT(@psql,
CASE WHEN typ_name IS NOT NULL
THEN (CONCAT(',sum(case typ_name when ''',typ_name,
''' then 1 else 0 end) as ',typ_name,' '))
ELSE (', sum(case when typ_name is null then 1 else 0 end) as 未选择')
END)
FROM
(select typ_name from cus_cor_cus
left join type_list on cor_typ_id=typ_id
where cor_isdelete='1' group by typ_name order by typ_name desc)
AS a;
SET @psql:=CONCAT(@psql,
',count(cor_code)as 合计 from cus_cor_cus
inner join sal_emp on cor_se_no=se_no
left join type_list on cor_typ_id=typ_id
where cor_isdelete=''1'' group by se_no with rollup');
PREPARE stmt2 FROM @psql;
EXECUTE stmt2;
END
解决方案 »
- Incorrect string value: '\xE5\x93\x88%'' for column 'sqlWhere' at row 1
- 这个视图怎么写?
- postgresql 安装失败?求教
- 为什么不建议改mysql的my.ini中的default charset为非默认
- mysql有没有except语句啊
- 求助:學生成績排名的列表SQL語句
- 初学mysql,问个简单的问题,如何建数据库啊?
- 请问没有相同条件,能否将两个表的数据链到一起呢?!(join)
- 查询运行时间
- Mysql怎么查询100个表格里的数据
- 怎么获取刚才查询的 记录的 count(*)
- 最常用的linux是什么版本的?
set names gbk;
我后来把中文都换成英文的还是不行
SELECT @psql:=CONCAT(@psql,
CASE WHEN typ_name IS NOT NULL...
一堆字符串,而不是执行了EXECUTE stmt2的结果集,如果把中间的这条SELECT @psql:=CONCAT(@psql... 去掉是能返回EXECUTE stmt2的结果集的,如果加上返回的就是
+---------------------------------------------------------------------------------------+
@psql:=CONCAT(@psql, CASE WHEN typ_name IS NOT NULL THEN (CONCAT(',sum(case typ_name when ''',typ_name, ''' then 1 else 0 end) as ',typ_name,' ')) ELSE (', sum(case when typ_name is null then 1 else 0 end) as 未选择') END)
+---------------------------------------------------------------------------------------+
select se_no,max(se_code),max(se_name) as head,sum...
select se_no,max(se_code),max(se_name) as head,sum...
CASE WHEN typ_name IS NOT NULL
THEN (CONCAT(',sum(case typ_name when ''',typ_name,
''' then 1 else 0 end) as ',typ_name,' '))
ELSE (', sum(case when typ_name is null then 1 else 0 end) as 未选择')
END)
FROM
(select typ_name from cus_cor_cus
left join type_list on cor_typ_id=typ_id
where cor_isdelete='1' group by typ_name order by typ_name desc)
AS a;
这是SQLSERVER的写法,在MYSQL中
打开select typ_name from cus_cor_cus
left join type_list on cor_typ_id=typ_id
where cor_isdelete='1' group by typ_name order by typ_name desc
游标,循环取值,
给@psql赋值
在SQL语句中加入CREATE TABLE TT AS
直接访问TT表