mysql - 如何像excel中的数据透视表一样在mysql中显示数据

Question

我有这样的查询:

SELECT DATE,REGION,COUNT(*)
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,DATE DESC

结果是 像下面的捕获这样显示的正确查询是什么

有人可以帮助我吗？

Answer 1

这种类型的查询称为 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;