MySql Povit,[MySQL|Postgresql] Pivot 通用技巧

在Oracle和 SQLServer 都有現成的PIvot樞紐分析函式可以用

在MySQL 中如果要使用 樞紐分析 怎麼辦...

今天和大家分享如何在MySQL中使用樞紐分析

案例樣本資料：

CREATE TABLE T(

userName VARCHAR(100),

Price int,

Dt DATE

);

INSERT INTO T VALUES ('Tom',100,'2017-01-01');

INSERT INTO T VALUES ('Amy',200,'2017-01-02');

INSERT INTO T VALUES ('Tom',1311,'2017-01-03');

INSERT INTO T VALUES ('Tom',122,'2017-03-01');

INSERT INTO T VALUES ('Tom',111,'2017-04-01');

INSERT INTO T VALUES ('Amy',232,'2017-05-01');

INSERT INTO T VALUES ('Tom',2312,'2017-05-02');

INSERT INTO T VALUES ('Tom',23,'2017-05-03');

期望輸出：

SQLServer Version

在SQLServr中有Pivot  可以這樣做

SELECT userName,

coalesce(p1.[201701],0) '201701',

coalesce(p1.[201702],0) '201702',

coalesce(p1.[201703],0) '201703',

coalesce(p1.[201704],0) '201704',

coalesce(p1.[201705],0) '201705',

coalesce(p1.[201706],0) '201706'

FROM (

SELECT userName,Price,FORMAT(Dt,'yyyyMM') Dt

FROM T

) t

PIVOT (

SUM(Price) FOR Dt

IN ([201701],[201702],[201703],[201704],[201705],[201706])

)p1

MySQL Version

Mysql中 使用PIVOT 我們可以使用 聚集函式 + CASE WHEN表達式來達成

SELECT userName,

SUM(CASE WHEN date_format(Dt,'%Y%m') = '201701' THEN Price ELSE 0 END) `201701`,

SUM(CASE WHEN date_format(Dt,'%Y%m') = '201702' THEN Price ELSE 0 END) `201702`,

SUM(CASE WHEN date_format(Dt,'%Y%m') = '201703' THEN Price ELSE 0 END) `201703`,

SUM(CASE WHEN date_format(Dt,'%Y%m') = '201704' THEN Price ELSE 0 END) `201704`,

SUM(CASE WHEN date_format(Dt,'%Y%m') = '201705' THEN Price ELSE 0 END) `201705`,

SUM(CASE WHEN date_format(Dt,'%Y%m') = '201706' THEN Price ELSE 0 END) `201706`

FROM T

GROUP BY userName

聚集函式 + CASE WHEN 好看又通用 可在各個資料庫中使用(支援聚集函式和CASE WHEN就可用XD)

一般我在任何地方寫PIVOT 都會先使用這種方法^^

Mysql 動態產生 pivot

要動態產生Pivot表最關鍵，有兩個知識點

動態組出SQL語法

動態執行SQL語法

如果要動態執行SQL在Mysql中有三個關鍵字需要使用到

下面語法是動態產生SQL語法給@sql變數

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'SUM(CASE WHEN ''',

date_format(Dt,'%Y%m'),

''' THEN Price ELSE 0 END) AS `',

date_format(Dt,'%Y%m'), '`'

)

) INTO @sql

FROM T;

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

FROM T

GROUP BY userName;');

產生SQL如下

SELECT userName, SUM(CASE WHEN '201701' THEN Price ELSE 0 END) AS `201701`,SUM(CASE WHEN '201703' THEN Price ELSE 0 END) AS `201703`,SUM(CASE WHEN '201704' THEN Price ELSE 0 END) AS `201704`,SUM(CASE WHEN '201705' THEN Price ELSE 0 END) AS `201705`

FROM T

GROUP BY userName;

最後在動態執行SQL使用

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

完整SQL如下

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'SUM(CASE WHEN ''',

date_format(Dt,'%Y%m'),

''' THEN Price ELSE 0 END) AS `',

date_format(Dt,'%Y%m'), '`'

)

) INTO @sql

FROM T;

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

FROM T

GROUP BY userName;');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

如果本文對您幫助很大，可街口支付斗內鼓勵石頭^^