zhangzhufu12 发表于 2014-12-6 11:19:54

mysql行转列的处理(存储过程+预制语句+动态SQL)

本帖最后由 zhangzhufu12 于 2014-12-6 11:21 编辑

个人总结的关于mysql行转列中动态拼接SQL语句的实例(无参数)拿来与大家分享:

BEGIN
/*测试行转列 测试表 t2,测试数据:*/
/*
转载请注明出处,谢谢!
创建人:zhangzhufu 2014-12-4 14:10:54
*/
/*
伪代码:
1.从表中获取动态列名和动态列值
2.使用group_concat函数组合列名,组合列值
3.使用函数统计列名个数
4.使用函数拆分列名用于拼接创建临时表sql语句
5.使用函数拆分列值用于拼接向临时表插入数据语句
6.遍历临时表
备注:1.预制语句必须用用户变量来接收
         2.预制语句必须满足标准sql语法,非数字类型变量必须加单双引号,区别使用
         3.预制语句必须为单一sql语句
         4.预制语句中包含不定列的sql子句必须事先将其设置为空字符串,不可默认使用null
*/
DECLARE c               VARCHAR(255)    DEFAULT NULL;-- 接收课程名称
DECLARE s               VARCHAR(255)    DEFAULT NULL;-- 接收成绩
DECLARE k               INT                        DEFAULT 0;          -- 列名称数量
DECLARE varg            INT                        DEFAULT 1;          -- 变量g
DECLARE sql2             VARCHAR(255)   DEFAULT '';         -- 动态sql子句

#获取列名列值字符串
SELECT GROUP_CONCAT(crouse),GROUP_CONCAT(scord) INTO c,s FROM t2 WHERE 1 = 1;
#获取列个数
SET k = (length(c)-length(replace(c,',',''))+1);

#循环拼接动态sql语句
WHILE varg <= k DO
      # 拼写SQL子句,返回结果为一行记录
      SET sql2 = CONCAT(sql2,',',mysql_splitString(s,varg),' ','as',' ',mysql_splitString(c,varg));
      SET varg = varg+1; -- 循环条件
END WHILE;
#消除第一个逗号
SET sql2 = SUBSTR(sql2,2);
#拼接动态sql总句
SET @sql1 = CONCAT('CREATE TEMPORARY TABLE pkd select ', sql2);

#预执行动态SQL语句
PREPARE rowtocolumn FROM @sql1 ;
#预览sql1 = CREATE TEMPORARY TABLE pkd select 66 as 语文,77 as 数学,88 as 英语,99 as 物理;
#执行动态sql
EXECUTE rowtocolumn;
#遍历临时表
SELECT * FROM pkd;
#删除临时表
DROP TABLE pkd;
#解除预处理
DEALLOCATE PREPARE rowtocolumn;

#SELECT sql1;

END


注:表结构图片添加有误,如有需要请查看原地址
引用地址:http://zhangzhufu12.blog.163.com/blog/static/1634120252014116105734264/

页: [1]
查看完整版本: mysql行转列的处理(存储过程+预制语句+动态SQL)