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>
