MySQL 在数据透视表上选择非空列

Question

我在理解数据透视表方面仍然遇到一些麻烦，但我设法完成了这个工作查询:

select region,
sum(case when rescheduleCause = 'CLOSED' then round(reschedulePercentage,2) end) as 'CLOSED',
sum(case when rescheduleCause = 'RESHUFFLE' then round(reschedulePercentage,2) end) as 'RESHUFFLE',
sum(case when rescheduleCause = 'NEW PRIORITY' then round(reschedulePercentage,2) end) as 'NEW PRIORITY'

from fancy_table
group by region

我得到以下结果，计算正确:

region  | CLOSED | RESHUFFLE | NEW PRIORITY
___________________________________________
RegionA | 23.08  | NULL      | 38.46
RegiobB | 23.08  | NULL      | 7.69

我的问题在于 RESHUFFLE 列充满了 NULL 值。我想要的结果集如下所示:

region  | CLOSED | NEW PRIORITY
___________________________________________
RegionA | 23.08  | 38.46
RegiobB | 23.08  | 7.69

我知道如果我使用动态sql将此查询修改为存储过程就可以实现这一点，但是我需要的列与这些列一样有限，所以我认为这并不是真的那么必要。我试过:

    ifnull(sum(case when rescheduleCause = 'RESHUFFLE' then round(reschedulePercentage,2) end),0) as 'RESHUFFLE',
where `RESHUFFLE` is not null
where 'RESHUFFLE' is not null
having `RESHUFFLE` is not null

我没主意了。

Answer 1

当您在 SQL 中查询时，您需要提前知道列数，不能立即排除列。如果您的列数未知，那么您需要使用动态 SQL 首先创建要执行的 sql 字符串。

在 MySQL 中，这可以使用准备好的语句来完成。这将首先查询您的表以仅返回您实际拥有的 rescheduleCause 值。这些值连接在一起形成一个 sql 字符串，然后您将使用该字符串创建要执行的最终查询:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when rescheduleCause = ''',
      rescheduleCause,
      ''' then round(reschedulePercentage,2) end) AS ',
      replace(rescheduleCause, ' ', '')
    )
  ) INTO @sql
from fancy_table;

SET @sql = CONCAT('SELECT region, ', @sql, ' 
                    from fancy_table
                    group by region');

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

参见SQL Fiddle with Demo