php - MYSQL 创建动态查询(数据透视)

Question

我想获取按日期分组的 cmb_scenarios。即类似

+----------------------------------+------+-------+-------+-------+
| cmb_scenario                     | 2017-05-10 | 2017-05-12 | 2017-06-7 | 2018-01-01 |
+----------------------------------+------+-------+-------+-------+
| scenario                                |    0 |     0 |     0 |     1 |
| scenario1                       | 1047 |   549 |   637 |   492 |
| scenario2                       |    0 |     1 |     2 |     5 |
| scenario3                       |    0 |     0 |     0 |     1 |
| scenario4                       |   23 |     3 |     1 |     0 |

但是执行此查询时我不断收到“

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'scenario1,SUM( IF(DATE(datestamp) = 2018-01-08, 1,0) ) AS _NULL,SUM( IF(DATE' at".

我遵循了本教程 here我的查询如下所示:

SET @sql_dynamic = (SELECT GROUP_CONCAT( DISTINCT CONCAT('SUM( IF(DATE(datestamp) = ', DATE(datestamp), ', 1,0) ) AS _', DATE(datestamp))) FROM leases);

ET @sql = CONCAT('SELECT cmb_scenario, ', @sql_dynamic, ' FROM leases GROUP BY cmb_scenario WITH ROLLUP');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

我不知道我做错了什么，因为我照原样遵循了教程。

库姆是:

datestamp: yyyy-mm-dd HH:mm:ss
cmb_scenario: text

Answer 1

您需要在= ', DATE(datestamp) 的日期(datestamp) 部分周围连接引号，并且需要替换AS _', DATE(datestamp) 中返回的减号(-)