mysql 行转列存储过程_写了一个MySQL的行转列的存储过程

Bertha 。 2023-01-11 13:16 134阅读 0赞

网上的都是一些静态的，用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;

set @stmt3 = concat('select ',@temp,' from ',$schema\_name,'.',$table\_name,' limit ',j,',1 into @temp2;');

prepare s3 from @stmt3;

execute s3;

set str = concat(str,'"',@temp2,'"',',');

deallocate prepare s3;

set @stmt3 = '';

set j = j+1;

end loop loop3;

set str = left(str,length(str)-1);

\-- insert new data into table

set @stmt4 = concat('insert into test.temp',str,';');

prepare s4 from @stmt4;

execute s4;

deallocate prepare s4;

set @stmt4 = '';

set s=s+1;

end loop loop2;

END$$

DELIMITER ;

以下是测试结果：

======

select \* from a;

select \* from b;

select \* from salary;

call sp\_row\_column\_wrap('test','a');

select \* from test.temp;

call sp\_row\_column\_wrap('test','b');

select \* from test.temp;

call sp\_row\_column\_wrap('test','salary');

select \* from test.temp;

query result(2 records)

aid title

1   111

2   222

query result(3 records)

bid aid image   time

1   2   1.gif   2007-08-08

2   2   2.gif   2007-08-09

3   2   3.gif   2007-08-08

query result(7 records)

id  cost    des Autoid

1   10  aaaa    1

1   15  bbbb    2

1   20  cccc    3

2   80  aaaa    4

2   100 bbbb    5

2   60  dddd    6

3   500 dddd    7

query result(2 records)

1   2

111 222

query result(4 records)

1   2   3

2   2   2

1.gif   2.gif   3.gif

2007-08-08  2007-08-09  2007-08-08

query result(4 records)

1   2   3   4   5   6   7

1   1   1   2   2   2   3

10  15  20  80  100 60  500

aaaa    bbbb    cccc    aaaa    bbbb    dddd    dddd

1   2   3   4   5   6   7

MySQL数据库存储过程SQL语句与特殊技巧

mysql 行转列存储过程_写了一个MySQL的行转列的存储过程

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Mysql 存储过程\Mybatis框架call调用实现动态行转列

相关 MySQL行转列

相关 MySQL存储过程的动态行转列

相关 mysql 行转列存储过程_写了一个MySQL的行转列的存储过程

相关 hive中实现行转列_如何在mysql数据库中用存储过程实现动态行转列？

相关 mysql 行转列 sum_mysql 行转列

相关 Oracle 存储过程实现行转列-pivot

相关 MySQL行转列

相关 mysql 行转列

相关 MySQL 行转列