MySQL存储过程实现行转列

php中文网

发布时间：2016-06-07 17:57:59

1835人浏览过

来源于php中文网

原创

把表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 ;