mysql实现动态行转列_MySQL的动态行转列的实现方法_MySQL

bitsCN.com

网上的都是一些静态的，用CASE WHEN结构实现。所以我写了一个动态的。

SP 代码：

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_row_column_wrap`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_row_column_wrap`(IN $schema_name varchar(64),

IN $table_name varchar(64))

BEGIN

declare cnt int(11);

declare $table_rows int(11);

declare i int(11);

declare j int(11);

declare s int(11);

declare str varchar(255);

-- Get the column number of the table

select count(1) from information_schema.columns where

table_schema=$schema_name and table_name=$table_name into cnt;

-- Get the row number of the table

select table_rows from information_schema.tables where

table_schema = $schema_name and table_name=$table_name into $table_rows;

-- Check whether the table exists or not

drop table if exists test.temp;

create table if not exists test.temp (`1` varchar(255) not null);

-- loop1 start

set i = 0;

loop1:loop

if i = $table_rows-1 then

leave loop1;

end if;

set @stmt1 = concat(’alter table test.temp add `’,i+2,’` varchar(255) not null’);

prepare s1 from @stmt1;

execute s1;

deallocate prepare s1;

set @stmt1 = ’’;

set i = i + 1;

end loop loop1;

-- loop1 end;

set s = 0;

-- loop2 start

loop2:loop

-- leave loop2

if s=cnt then

leave loop2;

end if;

set @stmt2 = concat(’select column_name from information_schema.columns where table_schema="’,$schema_name,

’" and table_name="’,$table_name,’" limit ’,s,’,1 into @temp;’);

prepare s2 from @stmt2;

execute s2;

deallocate prepare s2;

set @stmt2 = ’’;

set j=0;

set str = ’ select ’;

-- Loop3 start

loop3:loop

if j = $table_rows then

leave loop3;

end if;bitsCN.com

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

本文系统来源：php中文网