前文介紹了Oracle 中實(shí)現(xiàn)數(shù)據(jù)透視表的幾種方法，今天我們來看看在 MySQL/MariaDB 中如何實(shí)現(xiàn)相同的功能。

本文使用的示例數(shù)據(jù)可以點(diǎn)此下載。

數(shù)據(jù)透視表的本質(zhì)就是按照行和列的不同組合進(jìn)行數(shù)據(jù)分組，然后對(duì)結(jié)果進(jìn)行匯總；因此，它和數(shù)據(jù)庫中的分組（GROUP BY）加聚合函數(shù)（COUNT、SUM、AVG 等）的功能非常類似。

我們首先使用以下 GROUP BY 子句對(duì)銷售數(shù)據(jù)進(jìn)行分類匯總：

select coalesce(product, ’【全部產(chǎn)品】’) '產(chǎn)品', coalesce(channel, ’【所有渠道】’) '渠道', any_value(coalesce(extract(year_month from saledate), ’【所有月份】’)) '月份', sum(amount) '銷量'from sales_datagroup by product,channel,extract(year_month from saledate) with rollup;

以上語句按照產(chǎn)品、渠道以及月份進(jìn)行匯總；with rollup 選項(xiàng)用于生成不同層次的小計(jì)、合計(jì)以及總計(jì)；coalesce 函數(shù)用于將匯總行中的 NULL 值顯示為相應(yīng)的信息；any_value 函數(shù)用于返回分組內(nèi)的任意數(shù)據(jù)，如果去掉會(huì)返回語法錯(cuò)誤（MySQL 的一個(gè) bug）。該查詢返回的結(jié)果如下：

產(chǎn)品 |渠道 |月份 |銷量 |---------|---------|-----------|-------|桔子 |京東 |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|【全部產(chǎn)品】|【所有渠道】|【所有月份】|2771682|

實(shí)際上，我們已經(jīng)得到了銷量的匯總結(jié)果，只不過需要將數(shù)據(jù)按照不同月份顯示為不同的列；也就是需要將行轉(zhuǎn)換為列，這個(gè)功能可以使用 CASE 表達(dá)式實(shí)現(xiàn)：

select coalesce(product, ’【全部產(chǎn)品】’) '產(chǎn)品', coalesce(channel, ’【所有渠道】’) '渠道', sum(case extract(year_month from saledate) when 201901 then amount else 0 end) '一月', sum(case extract(year_month from saledate) when 201902 then amount else 0 end) '二月', sum(case extract(year_month from saledate) when 201903 then amount else 0 end) '三月', sum(case extract(year_month from saledate) when 201904 then amount else 0 end) '四月', sum(case extract(year_month from saledate) when 201905 then amount else 0 end) '五月', sum(case extract(year_month from saledate) when 201906 then amount else 0 end) '六月', sum(amount) '總計(jì)'from sales_datagroup by product, channel with rollup;

第一個(gè) SUM 函數(shù)中的 CASE 表達(dá)式只匯總 201901 月份的銷量，其他月份銷量設(shè)置為 0；后面的 SUM 函數(shù)依次類推，得到了每個(gè)月的銷量匯總和所有月份的總計(jì)。該查詢返回的數(shù)據(jù)透視表如下：

產(chǎn)品 |渠道 |一月 |二月 |三月 |四月 |五月 |六月 |總計(jì) |----------|----------|------|------|------|------|------|------|-------|桔子 |京東 | 41289| 43913| 49803| 49256| 64889| 62649| 311799|桔子 |店面 | 41306| 37906| 48866| 48673| 58998| 58931| 294680|桔子 |淘寶 | 43488| 37598| 48621| 49919| 58530| 64626| 302782|桔子 |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|蘋果 |京東 | 38269| 40593| 56552| 56662| 64493| 62045| 318614|蘋果 |店面 | 43845| 40539| 44909| 55646| 56771| 64933| 306643|蘋果 |淘寶 | 42969| 43289| 48769| 58052| 58872| 59844| 311795|蘋果 |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|香蕉 |京東 | 36879| 36981| 51748| 54801| 64936| 60688| 306033|香蕉 |店面 | 41210| 39420| 50884| 52085| 60249| 67597| 311445|香蕉 |淘寶 | 42468| 41955| 52780| 54971| 56504| 59213| 307891|香蕉 |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|【全部產(chǎn)品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|

MySQL 中的 IF(expr1,expr2,expr3) 函數(shù)也可以用于替換上面 CASE 表達(dá)式。

有行轉(zhuǎn)列就有列轉(zhuǎn)行，MySQL 也沒有專門的函數(shù)處理這種情況，可以使用 UNION 操作符將多個(gè)結(jié)果集進(jìn)行合并。例如：

with d as ( select product, channel, sum(case extract(year_month from saledate) when 201901 then amount else 0 end) s01, sum(case extract(year_month from saledate) when 201902 then amount else 0 end) s02, sum(case extract(year_month from saledate) when 201903 then amount else 0 end) s03, sum(case extract(year_month from saledate) when 201904 then amount else 0 end) s04, sum(case extract(year_month from saledate) when 201905 then amount else 0 end) s05, sum(case extract(year_month from saledate) when 201906 then amount else 0 end) s06 from sales_data group by product, channel)select product, channel, 201901 saledate, s01 amount from dunion allselect product, channel, 201902 saledate, s02 from dunion allselect product, channel, 201903 saledate, s03 from dunion allselect product, channel, 201904 saledate, s04 from dunion allselect product, channel, 201905 saledate, s05 from dunion allselect product, channel, 201906 saledate, s06 from d;

通用表表達(dá)（with 子句）構(gòu)造了包含多個(gè)月份的銷量數(shù)據(jù)，每個(gè)月份都是一列；然后每個(gè)查詢返回一個(gè)月份的數(shù)據(jù)，并且通過 union all 操作符將所有結(jié)果合并到一起。

使用 CASE 表達(dá)式和聚合函數(shù)實(shí)現(xiàn)數(shù)據(jù)透視表的方法存在一定的局限性，假如還有 7 月份到 12 月份的銷量需要統(tǒng)計(jì)，我們就需要修改查詢語句增加這部分的處理。為此，我們可以使用動(dòng)態(tài) SQL 自動(dòng)生成行列轉(zhuǎn)換的語句：

select group_concat( distinct concat( ’ sum(case extract(year_month from saledate) when ’, dt, ’ then amount else 0 end) as '’, dt, ’'’) ) into @sqlfrom ( select extract(year_month from saledate) as dt from sales_data order by saledate) d;set @sql = concat(’select coalesce(product, ’’【全部產(chǎn)品】’’) '產(chǎn)品', coalesce(channel, ’’【所有渠道】’’) '渠道',’, @sql, ’, sum(amount) '總計(jì)' from sales_data group by product, channel with rollup;’);select @sql;prepare stmt from @sql;execute stmt;deallocate prepare stmt;

首先，通過查詢 sales_data 表找出所有的月份并且構(gòu)造 sum 函數(shù)，將構(gòu)造的語句存入變量 @sql 中；group_concat 函數(shù)可以將多行字符串合并成單個(gè)字符串。

group_concat 函數(shù)允許返回的最大長度（字節(jié)）由系統(tǒng)變量 group_concat_max_len 進(jìn)行設(shè)置，默認(rèn)值為 1024。

然后，使用 set 命令將查詢語句的其他部分和已有的內(nèi)容進(jìn)行合并，生成的查詢語句如下：

select coalesce(product, ’【全部產(chǎn)品】’) '產(chǎn)品', coalesce(channel, ’【所有渠道】’) '渠道', sum(case extract(year_month from saledate) when 201901 then amount else 0 end) as '201901', sum(case extract(year_month from saledate) when 201902 then amount else 0 end) as '201902', sum(case extract(year_month from saledate) when 201903 then amount else 0 end) as '201903', sum(case extract(year_month from saledate) when 201904 then amount else 0 end) as '201904', sum(case extract(year_month from saledate) when 201905 then amount else 0 end) as '201905', sum(case extract(year_month from saledate) when 201906 then amount else 0 end) as '201906', sum(amount) '總計(jì)'from sales_datagroup by product, channel with rollup;

最后通過預(yù)編譯命令執(zhí)行該語句并返回結(jié)果，即使增加了其他月份的銷售數(shù)據(jù)也不需要手動(dòng)修改查詢語句。

如果使用 MariaDB 10.0 以上的版本，可以利用 CONNECT 存儲(chǔ)引擎中的 PIVOT 表類型實(shí)現(xiàn)數(shù)據(jù)透視表。

首先，我們需要安裝 CONNECT 存儲(chǔ)引擎。Windows 系統(tǒng)可以執(zhí)行以下命令進(jìn)行動(dòng)態(tài)安裝：

INSTALL SONAME ’ha_connect’;

也可以在配置文件 my.ini 中增加以下內(nèi)容，不過需要重啟服務(wù)：

[mysqld]plugin_load_add = ha_connect

對(duì)于 Linux 系統(tǒng)，安裝過程可以參考官方文檔。

接下來我們定義一個(gè) pivot 類型的表：

create table pivot_sales( product varchar(20) not null, channel varchar(20) not null, `201901` decimal(10,2) not null flag=1, `201902` decimal(10,2) not null flag=1, `201903` decimal(10,2) not null flag=1, `201904` decimal(10,2) not null flag=1, `201905` decimal(10,2) not null flag=1, `201906` decimal(10,2) not null flag=1)engine=connect table_type=pivotoption_list=’PivotCol=saledate,FncCol=amount,host=127.0.0.1,user=root, password=p123456,port=3306’SrcDef=’select product,channel,date_format(saledate, ’’%Y%m’’) saledate,sum(amount) amount from sales_data group by product,channel,date_format(saledate, ’’%Y%m’’)’;

其中，engine 定義存儲(chǔ)引擎為 connect；table_type 定義表的類型為 pivot；option_list 用于定義各種選項(xiàng)，PivotCol 表示要轉(zhuǎn)換成多個(gè)字段的數(shù)據(jù)所在的列，F(xiàn)ncCol 指定要進(jìn)行匯總的字段，其他是連接源表服務(wù)器的信息；SrcDef 用于指定源表查詢語句，也可以使用 Tabname 指定表名；上面的字段是透視表的結(jié)構(gòu)，flag=1 表示聚合之后的字段。

創(chuàng)建成功之后，我們就可以直接查詢 pivot_sales 表中的數(shù)據(jù)了：

select * from pivot_sales;product |channel |201901 |201902 |201903 |201904 |201905 |201906 |--------|---------|--------|--------|--------|--------|--------|--------|桔子 |京東 |41289.00|43913.00|49803.00|49256.00|64889.00|62649.00|桔子 |店面 |41306.00|37906.00|48866.00|48673.00|58998.00|58931.00|桔子 |淘寶 |43488.00|37598.00|48621.00|49919.00|58530.00|64626.00|蘋果 |京東 |38269.00|40593.00|56552.00|56662.00|64493.00|62045.00|蘋果 |店面 |43845.00|40539.00|44909.00|55646.00|56771.00|64933.00|蘋果 |淘寶 |42969.00|43289.00|48769.00|58052.00|58872.00|59844.00|香蕉 |京東 |36879.00|36981.00|51748.00|54801.00|64936.00|60688.00|香蕉 |店面 |41210.00|39420.00|50884.00|52085.00|60249.00|67597.00|香蕉 |淘寶 |42468.00|41955.00|52780.00|54971.00|56504.00|59213.00|

目前，PIVOT 表支持的功能有限，只能進(jìn)行一些基本的操作。例如：

-- 不會(huì)出錯(cuò)select * from pivot_saleswhere channel =’京東’;-- 語法錯(cuò)誤select channel from pivot_saleswhere channel =’京東’;

到此這篇關(guān)于MySQL/MariaDB 如何實(shí)現(xiàn)數(shù)據(jù)透視表的示例代碼的文章就介紹到這了,更多相關(guān)MySQL/MariaDB數(shù)據(jù)透視表內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！