将日期作为列的mysql动态交叉表

Question

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

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语法错误。有人能帮我找出什么是坏的吗？

Answer 1

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

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;

检查SQL Fiddle中的示例