MySQL存储过程实现行转列

把表t_rows中的数据转换为列显示
CREATE TABLE `t_rows` （
`dt_str` varchar（20） NOT NULL,
`name` varchar（20） NOT NULL,
`age` int（11） NOT NULL
） ENGINE=InnoDB DEFAULT CHARSET=utf8; 表t_rows行显示的结果为
mysql> SELECT `dt_str`, `name`, `age` FROM `t_rows`;
+----------+-----------+-----+
| dt_str | name | age |
+----------+-----------+-----+
| 20120610 | name_9881 | 81 |
| 20120609 | name_9882 | 82 |
| 20120608 | name_9883 | 83 |
| 20120607 | name_9884 | 84 |
| 20120606 | name_9885 | 85 |
| 20120605 | name_9886 | 86 |
| 20120604 | name_9887 | 87 |
| 20120603 | name_9888 | 88 |
| 20120602 | name_9889 | 89 |
| 20120601 | name_9890 | 90 |
| 20120531 | name_9891 | 91 |
+----------+-----------+-----+ 转换为图片“列显示.jpg”（文字格式有点乱，只好贴图了）的显示方式
dt_str 20120610 20120609 20120608 20120607 20120606 20120605 20120604 20120603 20120602 20120601 20120531
name name_9881 name_9882 name_9883 name_9884 name_9885 name_9886 name_9887 name_9888 name_9889 name_9890 name_9891
age 81 82 83 84 85 86 87 88 89 90 91 存储过程定义：
DELIMITER $$ DROP PROCEDURE IF EXISTS `pr_row_to_col`$$ CREATE DEFINER=`root`@`%` PROCEDURE `pr_row_to_col`（）
COMMENT "将表t_row中的3列（`dt_str`, `name`, `age`）数据转换为列显示"
proc_start:BEGIN DECLARE _end INT DEFAULT 0; -- 临时表名
DECLARE _TEMP_TB_NAME VARCHAR（255） DEFAULT "t_temp_rows_to_col"; -- 创建存储列数据的表结构sql
DECLARE _sql_create TEXT;
-- 每列数据的拼接的字符串，因为此例只查询3列（`dt_str`, `name`, `age`）数据
-- 假设每列所有行的拼接字符串不超过TEXT，如果超过可以使用longtext等
DECLARE _res_dt,_res_name,_res_age TEXT;
-- 每个数据的长度定义为varchar（255）,如果数据最大长度超过255，则改为最大值即可
DECLARE _dt_str,_name,_age VARCHAR（255） DEFAULT "";
-- 分隔符
DECLARE _SPLITER CHAR（1） DEFAULT ","; -- 查询所有行数据的游标
DECLARE _cur CURSOR FOR SELECT `dt_str`, `name`, `age` FROM t_rows;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _end=1; -- 打开游标
OPEN _cur; -- 初始化
SET _res_dt="";
SET _res_name="";
SET _res_age=""; -- drop临时表
SET @exe_str=CONCAT（"DROP TABLE IF EXISTS ",_TEMP_TB_NAME）;
PREPARE stmt FROM @exe_str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; -- 创建存储列数据的表结构sql
SET _sql_create=CONCAT（"create table ",_TEMP_TB_NAME,"（"）; SET _sql_create=CONCAT（_sql_create,"col0 VARCHAR（255） NOT NULL,"）;
SET @i=1;
rep_start:REPEAT
FETCH _cur INTO _dt_str, _name, _age;
IF _end=1 THEN
LEAVE rep_start;
END IF; -- 拼接每列数据的字符串
SET _res_dt=CONCAT（_res_dt,""",_dt_str,""",_SPLITER）;
SET _res_name=CONCAT（_res_name,""",_name,""",_SPLITER）;
SET _res_age=CONCAT（_res_age,""",_age,""",_SPLITER）; -- 拼接创建表结构字符串
SET _sql_create=CONCAT（_sql_create,"col",@i," VARCHAR（255） NOT NULL,"）; SET @i=@i+1; UNTIL _end=1 END REPEAT rep_start; -- 截取每个字符串最后的分隔符
SET _res_dt=SUBSTRING（_res_dt,1,（LENGTH（_res_dt）-1））;
SET _res_name=SUBSTRING（_res_name,1,（LENGTH（_res_name）-1））;
SET _res_age=SUBSTRING（_res_age,1,（LENGTH（_res_age）-1））;
SET _sql_create=SUBSTRING（_sql_create,1,（LENGTH（_sql_create）-1））; -- 拼接创建表结构字符串
SET _sql_create=CONCAT（_sql_create,"）ENGINE=MEMORY DEFAULT CHARACTER SET utf8"）; -- 关闭游标
CLOSE _cur; -- 创建列数据存储使用的临时表
SET @exe_str=_sql_create;
PREPARE stmt FROM @exe_str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; -- 插入列数据，在每列数据前插入了列名
SET @exe_str=CONCAT（"INSERT INTO ",_TEMP_TB_NAME," VALUES （"dt_str",",_res_dt,"）,（"name",",_res_name,"）,（"age",",_res_age,"）"）;
PREPARE stmt FROM @exe_str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; -- 输出行转列后的数据
SET @exe_str=CONCAT（"SELECT * FROM ",_TEMP_TB_NAME）;
PREPARE stmt FROM @exe_str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; END proc_start$$ DELIMITER ; 使用MySQL federated 引擎构建 MySQL 分布式数据库访问层MySQL判断字符串是否是数字相关资讯 MySQL存储过程

MySQL将表名称修改成大写的存储过（08月13日）
MySQL存储过程及触发器（12/15/2015 10:44:33）
MySQL存储过程以及在Java中的程序（08/03/2015 14:02:11）

MySQL创建存储过程实例（03月08日）
MySQL 存储过程学习（08/24/2015 19:55:43）
MySQL存储过程权限检查主要点（06/06/2013 19:42:04）

本文评论查看全部评论（0）