米琪卡哇伊
下面是存储过程,它将根据来自一个表和列的数据以及来自其他表和列的数据生成表。函数‘sum(if(coll=value,1,0)为值’。您可以从MAX(if()等不同的函数中进行选择。delimiter //
create procedure myPivot(
in tableA varchar(255),
in columnA varchar(255),
in tableB varchar(255),
in columnB varchar(255))begin
set @sql = NULL;
set @sql = CONCAT('select group_concat(distinct concat(
\'SUM(IF(',
columnA,
' = \'\'\',',
columnA,
',\'\'\', 1, 0)) AS \'\'\',',
columnA,
',\'\'\'\') separator \', \') from ',
tableA, ' into @sql');
-- select @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- select @sql;
SET @sql = CONCAT('SELECT p.',
columnB,
', ',
@sql,
' FROM ', tableB, ' p GROUP BY p.',
columnB,'');
-- select @sql;
/* */
PREPARE stmt FROM @sql;
EXECUTE stmt;
/* */
DEALLOCATE PREPARE stmt;end//delimiter ;