把表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
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 ;
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/467537.html
微信扫一扫 
支付宝扫一扫 
