MySQL/MariaDB 如何实现数据透视表

文章目录

        使用 CASE 表达式和分组聚合
        使用预编译的动态 SQL 语句
        使用 CONNECT 存储引擎

前文介绍了 Oracle 中实现数据透视表的几种方法，今天我们来看看在 MySQL/MariaDB 中如何实现相同的功能。

    📝本文使用的示例数据可以点此下载。

使用 CASE 表达式和分组聚合

数据透视表的本质就是按照行和列的不同组合进行数据分组，然后对结果进行汇总；因此，它和数据库中的分组（GROUP BY）加聚合函数（COUNT、SUM、AVG 等）的功能非常类似。

我们首先使用以下 GROUP BY 子句对销售数据进行分类汇总：

select coalesce(product, '【全部产品】') "产品",
       coalesce(channel, '【所有渠道】') "渠道",
       any_value(coalesce(extract(year_month from saledate), '【所有月份】')) "月份",
       sum(amount) "销量"
from sales_data
group by product,channel,extract(year_month from saledate) with rollup;


以上语句按照产品、渠道以及月份进行汇总；with rollup 选项用于生成不同层次的小计、合计以及总计；coalesce 函数用于将汇总行中的 NULL 值显示为相应的信息；any_value 函数用于返回分组内的任意数据，如果去掉会返回语法错误（MySQL 的一个 bug）。该查询返回的结果如下：

产品      |渠道      |月份       |销量    |
---------|---------|-----------|-------|
桔子      |京东      |201901    |  41289|
桔子      |京东      |201902    |  43913|
桔子      |京东      |201903    |  49803|
桔子      |京东      |201904    |  49256|
桔子      |京东      |201905    |  64889|
桔子      |京东      |201906    |  62649|
桔子      |京东      |【所有月份】| 311799|
桔子      |店面      |201901    |  41306|
桔子      |店面      |201902    |  37906|
桔子      |店面      |201903    |  48866|
桔子      |店面      |201904    |  48673|
桔子      |店面      |201905    |  58998|
桔子      |店面      |201906    |  58931|
桔子      |店面      |【所有月份】| 294680|
桔子      |淘宝      |201901    |  43488|
桔子      |淘宝      |201902    |  37598|
桔子      |淘宝      |201903    |  48621|
桔子      |淘宝      |201904    |  49919|
桔子      |淘宝      |201905    |  58530|
桔子      |淘宝      |201906    |  64626|
桔子      |淘宝      |【所有月份】| 302782|
桔子      |【所有渠道】|【所有月份】| 909261|
...
香蕉      |【所有渠道】|【所有月份】| 925369|
【全部产品】|【所有渠道】|【所有月份】|2771682|



实际上，我们已经得到了销量的汇总结果，只不过需要将数据按照不同月份显示为不同的列；也就是需要将行转换为列，这个功能可以使用 CASE 表达式实现：

 

更多请见：http://www.mark-to-win.com/tutorial/51579.html