PHP MySQL数据透视表代码 - PHP MySQL Pivot Table Code

我已经为mysql数据透视表编写了以下代码:

SET @SQL = NULL;
SET @@group_concat_max_len = 6000;
SELECT GROUP_CONCAT( DISTINCT CONCAT( 'MAX(IF(questiondetails = ''', questiondetails, ''', answer, null)) AS ''', questiondetails, ''' ' )) INTO @SQL FROM wtfeedback;
SET @SQL = CONCAT( 'SELECT trialid, productsku, userkey, category, ', @SQL, ' FROM wtfeedback GROUP BY trialid' );
PREPARE stmt FROM @SQL;
EXECUTE stmt;

这个工作在Sequel Pro (mysql gui编辑器)

但是当我粘贴到我的php页面上运行这段代码时，它显示了一个语法错误:

你的SQL语法有错误;查看对应MySQL服务器版本的手册，以便在SET @@group_concat_max_len = 6000附近使用正确的语法;SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(I' at line 3)

我正在努力看看错误可能是什么。

有什么想法吗?

MySQL不使用''转义单引号。如果您想在in-sql字符串中嵌入单引号，那么使用'':

CONCAT('MAX(IF(questiondetails = ''', questiondetails, ''', answer, null)) AS "', questiondetails, '" ')
                                 ^^---------------------^^

从PHP中，一次只发出一条语句。我推断这是问题所在，因为错误指向第二个SET的开始。

下面是一个为您生成pivot SELECT的存储过程

和盖伊·默里一样，我也遇到了同样的问题，但我努力解决了这个问题。基本上，我创建了一个存储过程，它允许您在可选择的行和列上运行数据透视表，并带有可选的过滤。它首先将"group by"选择查询的结果存储在一个临时表中，然后使用"group_concat"函数将结果放入一个数据透视表中。和盖伊的把戏一样。优点是它只遍历主表一次，如果主表中有无数的记录，这可能会节省时间。

下面是一个示例表:

CREATE TABLE `Data` (
`Period` INT(2) NOT NULL,
    `Product` VARCHAR(20) NOT NULL DEFAULT '',
    `Amount` DOUBLE NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=latin1;
INSERT INTO `Data` (`Period`, `Product`, `Amount`)
VALUES
    (1,'PrdA',15484),
    (1,'PrdA',45454),
    (1,'PrdB',478),
    (2,'PrdB',985),
    (2,'PrdB',741),
    (2,'PrdB',985),
    (3,'PrdA',7515),
    (3,'PrdA',454),
    (3,'PrdB',4584),
    (2,'PrdB',445),
    (1,'PrdB',669);

这是存储过程。代码中的附加注释。

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pivot`(
    source VARCHAR(1000), 
    val VARCHAR(40), 
    rws VARCHAR(40), 
    cls VARCHAR(40), 
    filter VARCHAR(1000))
BEGIN
/*
Creates a pivot table from any table, view or SQL statement. 
Mandatory: source, value, rows, and columns to be pivoted. 
Optional filtering.
Sample call strings: 
CALL pivot('data', 'amount', 'period', 'product', '');
CALL pivot('(select * from data)', 'amount', 'product', 'period', 'WHERE amount>1000');
*/

/*just to be sure*/
DROP TEMPORARY TABLE IF EXISTS temp1;
/*increase the value of group concat, otherwise the number of columns is very limited*/
SET SESSION group_concat_max_len = 100000;
/*perform a "select...group by" on the source and store it in a temp table1*/
SET @a=CONCAT(
    'CREATE TEMPORARY TABLE temp1 (
        SELECT ',
            rws,' AS rows, ',
            cls,' AS cols, 
            SUM(',val,') AS val 
        FROM ',source,' S ',
        filter, ' 
        GROUP BY '
            ,rws,', ',
            cls,');'
);
PREPARE stmt FROM @a;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
/*use "distinct columns" from temp1 to make a text string @coltext, that contains the column statements, to be used in the final step
Produced text string looks like this:      sum(CASE WHEN cols='PrdA' THEN val END) AS 'PrdA', sum(CASE WHEN cols='PrdB' THEN val END) AS 'PrdB'  */
SELECT GROUP_CONCAT(
    ' SUM(CASE WHEN cols=''',cols,''' THEN val END) AS ''',cols,'''') 
    INTO @coltext 
FROM (SELECT DISTINCT(cols) AS cols FROM temp1) A;
/*build the final statement in @b*/
SET @b=CONCAT(
    'SELECT 
        IFNULL(rows, ''Total'') AS ',rws,', '
        ,@coltext,',
        SUM(val) AS Total 
    FROM temp1 
    GROUP BY 
        rows 
    WITH ROLLUP;');
/*and launch it*/
PREPARE stmt FROM @b;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
/*clean up*/
DROP TEMPORARY TABLE IF EXISTS temp1;
SET @a=NULL;
SET @b=NULL;
SET @coltext=NULL;

END;;
DELIMITER ;

结果如下:

 period PrdA    PrdB    total
    1   60938   1147    62085
    2   NULL    3156    3156
    3   7969    4584    12553
 total  68907   8887    77794

希望这显示正确的堆栈溢出。这是我在这里的第一个帖子。

在这里阅读其他解决方案时，我意识到代码可以被清理和改进:它现在没有任何硬编码的引用。只要把它插入任何数据库，它就会工作。