php - Mysql 数据透视表没有显示结果

Question

我正在挖掘一些关于mysql中的数据透视表的信息，幸运的是我发现了一些东西并在 sqlfiddle 中测试了它。也在 wamp 控制台中:

两者都有不同的数据，因为我在 wamp 控制台中使用的数据是我的实际数据库。

不幸的是，当我在 php 中测试它时，没有得到与 sqlfiddle 和 wamp 控制台不同的结果。

这是我的脚本:

$link = mysqli_connect("localhost", "root", "", "dbmobile_class_record");

    $sql_pivot = "SET @sql = NULL;";
    $sql_pivot .= "SELECT
                  GROUP_CONCAT(
                    DISTINCT CONCAT(
                      'MAX(IF(criteria = ''',
                      criteria,
                      ''', score, NULL)) AS ',
                      CONCAT(',criteria,')
                    )
                  ) INTO @sql FROM tb_student_record;";
    $sql_pivot .= "SET @sql = CONCAT(
                  'SELECT stud_name, ', 
                  @sql, 
                  ' FROM tb_student_record GROUP BY stud_name'
                  );";
    $sql_pivot .= "PREPARE stmt FROM @sql;";
    $sql_pivot .= "EXECUTE stmt;";
    $sql_pivot .= "DEALLOCATE PREPARE stmt;";

    if (mysqli_multi_query($link, $sql_pivot)){
        do {
            if ($result = mysqli_store_result($link)){
                while ($row = mysqli_fetch_array($result)){
                    echo $row['criteria'];
                    echo $row['stud_name'];
                    echo $row['score'];
                }
                mysqli_free_result($result);
            }
        } while (mysqli_next_result($link));
    }

Answer 1

您应该更好地将 SQL 批处理封装到存储过程中，然后使用单个 CALL 语句运行它:

DELIMITER ;
DROP PROCEDURE IF EXISTS sp_pivot;
DELIMITER $
CREATE PROCEDURE sp_pivot()
 SQL SECURITY INVOKER
BEGIN
    SET @sql = NULL;

    SELECT
        GROUP_CONCAT(
            DISTINCT CONCAT(
                'MAX(IF(criteria = ''',
                criteria,
                ''', score, NULL)) AS ',
                CONCAT(',criteria,')
            )
        ) INTO @sql FROM tb_student_record;

    SET @sql = CONCAT(
        'SELECT stud_name, ',
        @sql,
        ' FROM tb_student_record GROUP BY stud_name'
    );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $
DELIMITER ;

来自 PHP:

mysqli_query($link, 'CALL sp_pivot();')

这种方法的主要优点是，您可以使用 MySQL Workbench 或 PHPStorm Database 工具等 SQL 工具轻松管理它，此外 PHP 和 SQL 代码都变得更易于阅读和支持。您也可以将程序的源代码存储在git下的.sql文件中。