mysql - MySQL动态交叉表，日期作为列

这是我的问题，假设我有一个如下所示的数据库表：
TBL:传入

ID | Type  | cdate
 1 | Type1 | 2014-07-01 6:00:00
 1 | Type2 | 2014-07-02 6:00:00
 1 | Type1 | 2014-07-03 6:00:00
 1 | Type3 | 2014-07-04 6:00:00
 1 | Type2 | 2014-07-04 6:00:00

我想要一个查询，该查询将日期显示为一列：

Type  |  7-1  |  7-2  |  7-3  |  7-4
Type1 |  1    |  0    |   1   |  0
Type2 |  0    |  1    |   0   |  1
Type3 |  0    |  0    |   0   |  1

我的sql代码是：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(case when date_format(cdate, '%m-%d') = ''',
      date_format(cdate, '%m-%d'),
      ''' then 1 end) AS ',
      replace(cdate, ' ', '')
    )
  ) INTO @sql
from incoming WHERE cdate BETWEEN '2014-07-01 5:00:00' AND '2014-07-04 5:00:00';

SET @sql = CONCAT('SELECT type, ', @sql, ' from incoming group by type');

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

我在运行脚本时收到一个sql语法错误。有谁能帮我弄清楚什么坏了吗？

最佳答案

您需要在表达式中转义单引号。
另外，为列创建一个更简单的别名，并将其括起来（以防万一）。

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(case when date_format(cdate, \'%m-%d\') = \'', -- Escape quotes here
      date_format(cdate, '%m-%d'),                          -- (I prefer to use
      '\' then 1 end) AS ',                                 -- \' instead of ''')
      '`', date_format(cdate, '%Y%m%d'), '`' -- Create a simpler alias for
                                             -- columns here, and don't forget
                                             -- to enclose the alias in
                                             -- back-ticks (`)
    )
  ) INTO @sql
from incoming;

SET @sql = CONCAT('SELECT type, ', @sql, ' from incoming group by type');

select @sql;

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

检查example in SQL Fiddle

关于mysql - MySQL动态交叉表，日期作为列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24680618/