以日期为列的mysql动态交叉表

Question

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

待定:传入

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;

检查 example in SQL Fiddle