mysql可以用pivot么_如何在excel中显示mysql中的数据，如pivot

此类查询称为PIVOT。不幸的是，MySQL没有PIVOT函数，因此您需要使用CASE语句和聚合函数来复制函数。

如果您知道要转换的列数，则可以对值进行硬编码：

select region,

SUM(CASE WHEN date = '2012-09-24' THEN 1 END) as `2012-09-24`,

SUM(CASE WHEN date = '2012-09-30' THEN 1 END) as `2012-09-30`,

SUM(CASE WHEN date = '2012-10-01' THEN 1 END) as `2012-10-01`

from ALL_ID_DATA

group by region;

select region,

COUNT(CASE WHEN date = '2012-09-24' THEN 1 ELSE null END) as `2012-09-24`,

COUNT(CASE WHEN date = '2012-09-30' THEN 1 ELSE null END) as `2012-09-30`,

COUNT(CASE WHEN date = '2012-10-01' THEN 1 ELSE null END) as `2012-10-01`

from ALL_ID_DATA

group by region;请参阅SQL Fiddle with Demo。

然后将其添加到现有查询中，它将是：

SELECT REGION,

SUM(CASE WHEN date = '2012-09-24' THEN 1 END) as `2012-09-24`,

SUM(CASE WHEN date = '2012-09-30' THEN 1 END) as `2012-09-30`,

SUM(CASE WHEN date = '2012-10-01' THEN 1 END) as `2012-10-01`

FROM ALL_ID_DATA

WHERE DATE in (SUBDATE(CURDATE(),1),SUBDATE(CURDATE(),2),SUBDATE(CURDATE(),8))

AND DIRECTION='inbound'

AND REASON_1 = 'complaint'

GROUP BY REGION要么

SELECT REGION,

COUNT(CASE WHEN date = '2012-09-24' THEN 1 ELSE null END) as `2012-09-24`,

COUNT(CASE WHEN date = '2012-09-30' THEN 1 ELSE null END) as `2012-09-30`,

COUNT(CASE WHEN date = '2012-10-01' THEN 1 ELSE null END) as `2012-10-01`

FROM ALL_ID_DATA

WHERE DATE in (SUBDATE(CURDATE(),1),SUBDATE(CURDATE(),2),SUBDATE(CURDATE(),8))

AND DIRECTION='inbound'

AND REASON_1 = 'complaint'

GROUP BY REGION现在，如果您有未知数量的日期转换为列，那么您可以使用prepared statements，您的查询将与此类似(请参阅SQL Fiddle with Demo)：

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'sum(case when date = ''',

date,

''' then 1 else 0 end) AS ''',

Date(date), ''''

)

) INTO @sql

FROM ALL_ID_DATA;

select @sql;

SET @sql = CONCAT('SELECT region, ', @sql, '

FROM ALL_ID_DATA

GROUP BY region');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;然后将原始查询放在预准备语句中，最终查询将是：

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'sum(case when date = ''',

date,

''' then 1 else 0 end) AS ''',

Date(date), ''''

)

) INTO @sql

FROM ALL_ID_DATA;

select @sql;

SET @sql = CONCAT('SELECT region, ', @sql, '

FROM ALL_ID_DATA

WHERE DATE in (SUBDATE(CURDATE(),1),SUBDATE(CURDATE(),2),SUBDATE(CURDATE(),8))

AND DIRECTION=''inbound''

AND REASON_1 = ''complaint''');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;