mysql 存储过程行转列_MySQL存储过程实现行转列

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中的数据转换为列显示

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 ;

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网