php 2007-11-7 09:01
写了一个MYSQL的行转列的存储过程
网上的都是一些静态的，用CASE WHEN结构实现。所以我写了一个动态的。
SP 代码：
[color=#000000]DELIMITER $$
[color=#0000ff]DROP[/color] PROCEDURE IF EXISTS [color=#ff00ff]`test`[/color][color=#0000cc].[/color][color=#ff00ff]`sp_row_column_wrap`[/color][color=#ff00ff]$[/color][color=#ff00ff]$[/color]
[color=#0000ff]CREATE[/color] DEFINER[color=#0000cc]=[/color][color=#ff00ff]`root`[/color][color=#0000ff]@[/color][color=#ff00ff]`localhost`[/color] PROCEDURE [color=#ff00ff]`sp_row_column_wrap`[/color][color=#0000cc]([/color][color=#0000ff]IN[/color] $schema_name [color=#ff0000]varchar[/color][color=#0000cc]([/color]64[color=#0000cc])[/color][color=#0000cc],[/color]
[color=#0000ff]IN[/color] $table_name [color=#ff0000]varchar[/color][color=#0000cc]([/color]64[color=#0000cc])[/color][color=#0000cc])[/color]
BEGIN
declare cnt [color=#ff0000]int[/color][color=#0000cc]([/color]11[color=#0000cc])[/color][color=#0000cc];[/color]
declare $table_rows [color=#ff0000]int[/color][color=#0000cc]([/color]11[color=#0000cc])[/color][color=#0000cc];[/color]
declare i [color=#ff0000]int[/color][color=#0000cc]([/color]11[color=#0000cc])[/color][color=#0000cc];[/color]
declare j [color=#ff0000]int[/color][color=#0000cc]([/color]11[color=#0000cc])[/color][color=#0000cc];[/color]
declare s [color=#ff0000]int[/color][color=#0000cc]([/color]11[color=#0000cc])[/color][color=#0000cc];[/color]
declare str [color=#ff0000]varchar[/color][color=#0000cc]([/color]255[color=#0000cc])[/color][color=#0000cc];[/color]
-- Get the column number of the [color=#0000ff]table[/color]
[color=#0000ff]select[/color] [color=#0000ff]count[/color][color=#0000cc]([/color]1[color=#0000cc])[/color] [color=#0000ff]from[/color] information_schema[color=#0000cc].[/color]columns [color=#0000ff]where[/color] table_schema[color=#0000cc]=[/color][color=#0000cc]$[/color]schema_name [color=#ff0000]and[/color] table_name[color=#0000cc]=[/color][color=#0000cc]$[/color]table_name [color=#0000ff]into[/color] cnt[color=#0000cc];[/color]
-- Get the row number of the [color=#0000ff]table[/color]
[color=#0000ff]select[/color] table_rows [color=#0000ff]from[/color] information_schema[color=#0000cc].[/color]tables [color=#0000ff]where[/color] table_schema [color=#0000cc]=[/color] $schema_name [color=#ff0000]and[/color] table_name[color=#0000cc]=[/color][color=#0000cc]$[/color]table_name [color=#0000ff]into[/color] $table_rows[color=#0000cc];[/color]
-- Check whether the [color=#0000ff]table[/color] exists [color=#ff0000]or[/color] [color=#ff0000]not[/color]
[color=#0000ff]drop[/color] [color=#0000ff]table[/color] if exists test[color=#0000cc].[/color]temp[color=#0000cc];[/color]
[color=#0000ff]create[/color] [color=#0000ff]table[/color] if [color=#ff0000]not[/color] exists test[color=#0000cc].[/color]temp [color=#0000cc]([/color][color=#ff00ff]`1`[/color] [color=#ff0000]varchar[/color][color=#0000cc]([/color]255[color=#0000cc])[/color] [color=#ff0000]not[/color] [color=#0000ff]null[/color][color=#0000cc])[/color][color=#0000cc];[/color]
-- loop1 start
[color=#0000ff]set[/color] i [color=#0000cc]=[/color] 0[color=#0000cc];[/color]
loop1:loop
if i [color=#0000cc]=[/color] $table_rows-1 [color=#0000ff]then[/color]
leave loop1[color=#0000cc];[/color]
[color=#0000ff]end[/color] if[color=#0000cc];[/color]
[color=#0000ff]set[/color] [color=#0000ff]@[/color][color=#008080]stmt1[/color] [color=#0000cc]=[/color] [color=#ff0000]concat[/color][color=#0000cc]([/color][color=#ff00ff]'alter table test.temp add `'[/color][color=#0000cc],[/color]i[color=#0000cc]+[/color]2[color=#0000cc],[/color][color=#ff00ff]'` varchar(255) not null'[/color][color=#0000cc])[/color][color=#0000cc];[/color]
prepare s1 [color=#0000ff]from[/color] [color=#0000ff]@[/color][color=#008080]stmt1[/color][color=#0000cc];[/color]
execute s1[color=#0000cc];[/color]
deallocate prepare s1[color=#0000cc];[/color]
[color=#0000ff]set[/color] i [color=#0000cc]=[/color] i [color=#0000cc]+[/color] 1[color=#0000cc];[/color]
[color=#0000ff]end[/color] loop loop1[color=#0000cc];[/color]
-- loop1 [color=#0000ff]end[/color][color=#0000cc];[/color]
[color=#0000ff]set[/color] s [color=#0000cc]=[/color] 0[color=#0000cc];[/color]
-- loop2 start
loop2:loop
-- leave loop2
if s[color=#0000cc]=[/color]cnt [color=#0000ff]then[/color]
leave loop2[color=#0000cc];[/color]
[color=#0000ff]end[/color] if[color=#0000cc];[/color]
[color=#0000ff]set[/color] [color=#0000ff]@[/color][color=#008080]stmt2[/color] [color=#0000cc]=[/color] [color=#ff0000]concat[/color][color=#0000cc]([/color][color=#ff00ff]'select column_name from information_schema.columns where table_schema="'[/color][color=#0000cc],[/color][color=#0000cc]$[/color]schema_name[color=#0000cc],[/color]
[color=#ff00ff]'" and table_name="'[/color][color=#0000cc],[/color][color=#0000cc]$[/color]table_name[color=#0000cc],[/color][color=#ff00ff]'" limit '[/color][color=#0000cc],[/color]s[color=#0000cc],[/color][color=#ff00ff]',1 into @temp;'[/color][color=#0000cc])[/color][color=#0000cc];[/color]
prepare s2 [color=#0000ff]from[/color] [color=#0000ff]@[/color][color=#008080]stmt2[/color][color=#0000cc];[/color]
execute s2[color=#0000cc];[/color]
deallocate prepare s2[color=#0000cc];[/color]
[color=#0000ff]set[/color] j[color=#0000cc]=[/color]0[color=#0000cc];[/color]
[color=#0000ff]set[/color] str [color=#0000cc]=[/color] [color=#ff00ff]' select '[/color][color=#0000cc];[/color]
-- Loop3 start
loop3:loop
if j [color=#0000cc]=[/color] $table_rows [color=#0000ff]then[/color]
leave loop3[color=#0000cc];[/color]
[color=#0000ff]end[/color] if[color=#0000cc];[/color]
[color=#0000ff]set[/color] [color=#0000ff]@[/color][color=#008080]stmt3[/color] [color=#0000cc]=[/color] [color=#ff0000]concat[/color][color=#0000cc]([/color][color=#ff00ff]'select '[/color][color=#0000cc],[/color][color=#0000ff]@[/color][color=#008080]temp[/color][color=#0000cc],[/color][color=#ff00ff]' from '[/color][color=#0000cc],[/color][color=#0000cc]$[/color]schema_name[color=#0000cc],[/color][color=#ff00ff]'.'[/color][color=#0000cc],[/color][color=#0000cc]$[/color]table_name[color=#0000cc],[/color][color=#ff00ff]' limit '[/color][color=#0000cc],[/color]j[color=#0000cc],[/color][color=#ff00ff]',1 into @temp2;'[/color][color=#0000cc])[/color][color=#0000cc];[/color]
prepare s3 [color=#0000ff]from[/color] [color=#0000ff]@[/color][color=#008080]stmt3[/color][color=#0000cc];[/color]
execute s3[color=#0000cc];[/color]
[color=#0000ff]set[/color] str [color=#0000cc]=[/color] [color=#ff0000]concat[/color][color=#0000cc]([/color]str[color=#0000cc],[/color][color=#ff00ff]'"'[/color][color=#0000cc],[/color][color=#0000ff]@[/color][color=#008080]temp2[/color][color=#0000cc],[/color][color=#ff00ff]'"'[/color][color=#0000cc],[/color][color=#ff00ff]','[/color][color=#0000cc])[/color][color=#0000cc];[/color]
deallocate prepare s3[color=#0000cc];[/color]
[color=#0000ff]set[/color] j [color=#0000cc]=[/color] j[color=#0000cc]+[/color]1[color=#0000cc];[/color]
[color=#0000ff]end[/color] loop loop3[color=#0000cc];[/color]
[color=#0000ff]set[/color] str [color=#0000cc]=[/color] [color=#ff0000]left[/color][color=#0000cc]([/color]str[color=#0000cc],[/color][color=#0000ff]length[/color][color=#0000cc]([/color]str[color=#0000cc])[/color][color=#0000cc]-[/color]1[color=#0000cc])[/color][color=#0000cc];[/color]
-- [color=#0000ff]insert[/color] new data [color=#0000ff]into[/color] [color=#0000ff]table[/color]
[color=#0000ff]set[/color] [color=#0000ff]@[/color][color=#008080]stmt4[/color] [color=#0000cc]=[/color] [color=#ff0000]concat[/color][color=#0000cc]([/color][color=#ff00ff]'insert into test.temp'[/color][color=#0000cc],[/color]str[color=#0000cc],[/color][color=#ff00ff]';'[/color][color=#0000cc])[/color][color=#0000cc];[/color]
prepare s4 [color=#0000ff]from[/color] [color=#0000ff]@[/color][color=#008080]stmt4[/color][color=#0000cc];[/color]
execute s4[color=#0000cc];[/color]
deallocate prepare s4[color=#0000cc];[/color]
[color=#0000ff]set[/color] s[color=#0000cc]=[/color]s[color=#0000cc]+[/color]1[color=#0000cc];[/color]
[color=#0000ff]end[/color] loop loop2[color=#0000cc];[/color]
[color=#0000ff]END[/color][color=#0000ff]$[/color][color=#0000ff]$[/color]
DELIMITER [color=#0000cc];[/color][/color]
以下是测试结果：
======
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)
[table][tr][td]aid[/td][td]title[/td][/tr][tr][td]1[/td][td]111[/td][/tr][tr][td]2[/td][td]222[/td][/tr][/table]
query result(3 records)
[table][tr][td]bid[/td][td]aid[/td][td]image[/td][td]time[/td][/tr][tr][td]1[/td][td]2[/td][td]1.gif[/td][td]2007-08-08[/td][/tr][tr][td]2[/td][td]2[/td][td]2.gif[/td][td]2007-08-09[/td][/tr][tr][td]3[/td][td]2[/td][td]3.gif[/td][td]2007-08-08[/td][/tr][/table]
query result(7 records)
[table][tr][td]id[/td][td]cost[/td][td]des[/td][td]Autoid[/td][/tr][tr][td]1[/td][td]10[/td][td]aaaa[/td][td]1[/td][/tr][tr][td]1[/td][td]15[/td][td]bbbb[/td][td]2[/td][/tr][tr][td]1[/td][td]20[/td][td]cccc[/td][td]3[/td][/tr][tr][td]2[/td][td]80[/td][td]aaaa[/td][td]4[/td][/tr][tr][td]2[/td][td]100[/td][td]bbbb[/td][td]5[/td][/tr][tr][td]2[/td][td]60[/td][td]dddd[/td][td]6[/td][/tr][tr][td]3[/td][td]500[/td][td]dddd[/td][td]7[/td][/tr][/table]
query result(2 records)
[table][tr][td]1[/td][td]2[/td][/tr][tr][td]1[/td][td]2[/td][/tr][tr][td]111[/td][td]222[/td][/tr][/table]
query result(4 records)
[table][tr][td]1[/td][td]2[/td][td]3[/td][/tr][tr][td]1[/td][td]2[/td][td]3[/td][/tr][tr][td]2[/td][td]2[/td][td]2[/td][/tr][tr][td]1.gif[/td][td]2.gif[/td][td]3.gif[/td][/tr][tr][td]2007-08-08[/td][td]2007-08-09[/td][td]2007-08-08[/td][/tr][/table]
query result(4 records)
[table][tr][td]1[/td][td]2[/td][td]3[/td][td]4[/td][td]5[/td][td]6[/td][td]7[/td][/tr][tr][td]1[/td][td]1[/td][td]1[/td][td]2[/td][td]2[/td][td]2[/td][td]3[/td][/tr][tr][td]10[/td][td]15[/td][td]20[/td][td]80[/td][td]100[/td][td]60[/td][td]500[/td][/tr][tr][td]aaaa[/td][td]bbbb[/td][td]cccc[/td][td]aaaa[/td][td]bbbb[/td][td]dddd[/td][td]dddd[/td][/tr][tr][td]1[/td][td]2[/td][td]3[/td][td]4[/td][td]5[/td][td]6[/td][td]7[/td][/tr][/table]
[[i] 本帖最后由 php 于 2007-11-7 09:11 编辑 [/i]]