PHP 和 MYSQL 中的交叉表查询
我正在寻找一种在 MySQL 中实现交叉表查询的方法，并从这篇文章中找到了答案：在 MySQL 中将行中的日期显示为动态列。我尝试了该查询并让它工作，但如何使用 MySQL 过程风格在 PHP 中编写查询。以下是查询：
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(`week_start` = "', `week_start`,'", `weekly_value`,0)) AS "',DATE(`week_start`),'"')
) INTO @sql
FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK AND NOW()) t1;
SET @sql = CONCAT('SELECT s.`ID`, s.`name`, ', @sql, '
FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK AND NOW()) s
GROUP BY s.`name`
ORDER BY s.`ID`');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
$sql = "SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(`week_start` = \"', `week_start`,'\", `weekly_value`,0)) AS \"',DATE(`week_start`),'\"')
) INTO @sql
FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK AND NOW()) t1;
SET @sql = CONCAT('SELECT s.`ID`, s.`name`, ', @sql, '
FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK AND NOW()) s
GROUP BY s.`name`
ORDER BY s.`ID`');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;";
// echo $sql;
// exit;
$report_query = mysqli_query($conn, $sql);
如果我打印出生成的 sql 并将其粘贴到 PHPMyAdmin 中，查询执行正常，但在 php 上给出 SQL 语法错误“致命错误：未捕获 mysqli_sql_exception：您的 SQL 语法中有错误；检查相应的手册到您的 MariaDB 服务器版本，以获取在 'SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(week_start =...' at line 2 in..") 附近使用的正确语法。有没有办法在 php 中编写查询？< /p>
