iT邦幫忙

0

MYSQL 以月為欄位名稱的 月資料統計(table行列完全置換)

各位前輩們好
我是剛學習資訊工程的超級菜鳥
想在這邊詢問我寫sql遇到的難關~

目前再幫公司整理資料庫資料統計成月報表

整理出來的型態長下列這樣(此資料內容適用 month() 搜集出來的):
month | useMins | sumCase | sumPer |sumChat | sumRating
6 | 42 | 38 | 2 | 200 | 798
7 | 0 | 7 | 1 | 2 | 5

但我需要的型態為這樣:
name | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 |12
useMins | 0 | 0 | 0 | 0 | 0 | 42 | 0 | 0 | 0 | 0 | 0 | 0
sumCase | 0 | 0 | 0 | 0 | 0 | 38 | 7 | 0 | 0 | 0 | 0 | 0
sumPer | 0 | 0 | 0 | 0 | 0 | 2 | 1 | 0 | 0 | 0 | 0 | 0
sumChat | 0 | 0 | 0 | 0 | 0 | 200| 2 | 0 | 0 | 0 | 0 | 0
sumRating | 0 | 0 | 0 | 0 | 0 | 798| 5 | 0 | 0 | 0 | 0 | 0

一般網路上查的行列轉換
最後都還會有一列是無法轉換的
但我需要全部都轉換且能新增一列名為name的欄位
請問大神們有什麼方法可以做到嗎?

看更多先前的討論...收起先前的討論...
rogeryao iT邦大師 1 級 ‧ 2021-09-30 09:19:04 檢舉
MYSQL 版本 ?
請貼上你的 SQL,並將測試資料建於 https://dbfiddle.uk/?rdbms=mysql_8.0
pppppeter iT邦新手 5 級 ‧ 2021-09-30 09:54:07 檢舉
抱歉,我實在太菜了連MYSQL是哪個版本都不知道怎麼找,
我是使用phpMyAdmin
它側邊有資訊欄位:

伺服器: Localhost via UNIX socket
伺服器類型: MySQL
伺服器連線: 未有使用 SSL 說明文件
伺服器版本: 5.7.33 - MySQL Community Server (GPL)
協定版本: 10
伺服器字元集: cp1252 West European (latin1)

Apache/2.4.38 (Debian)
資料庫用戶端版本: libmysql - mysqlnd 7.4.15
PHP 擴充套件: mysqli 說明文件 mbstring 說明文件
PHP 版本: 7.4.15

版本資訊: 5.0.4deb2~bpo10+1

請問上面有你要的資訊嗎?
然後我不太知道怎麼將測試資料建立於你給我的網址 TAT
補助表
https://ithelp.ithome.com.tw/articles/10137154
rogeryao iT邦大師 1 級 ‧ 2021-09-30 10:09:56 檢舉
MYSQL 版本 : 5.7.33
請參閱 : https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=bf05fd86aa69ad06c371dec77053e790
pppppeter iT邦新手 5 級 ‧ 2021-09-30 11:16:06 檢舉
給 一級屠豬士:
「補助表」試過了,但卡在我原始資料的 product_name是在欄位標題,而不是欄位資料 TAT

給 rogeryao:
網址
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=4739cb4cf35a621169ddec8b425521a0
我有寫進去了 但不知道為什麼右邊沒有跑出table
Hankz iT邦新手 3 級 ‧ 2021-09-30 11:37:04 檢舉
如果只是要有結果 而不是一定要用SQL做的話
可以把MySQL資料以CSV匯出後
再用Excel把行列互換
也是一種方式
pppppeter iT邦新手 5 級 ‧ 2021-09-30 11:43:20 檢舉
是要寫進php 的 TAT
老闆在幫我嘗試用php 轉換
但總覺得 sql 應該有解法
但我解三天了...
我替你改好了
不過我猜這不是你的原始資料(eg.用 month SELECT 的那個 table)
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=c46f22e5b42c714420887f1ef9aeeaef
pppppeter iT邦新手 5 級 ‧ 2021-09-30 12:02:35 檢舉
謝謝 海綿寶寶
這的確不是我的原始資料
因為併入了七個table還包含了left join 寫進的暫時table
資料複雜龐大
想說先能簡單解決行列完全轉換的問題
再來往下試試看

2 個回答

0
rogeryao
iT邦大師 1 級 ‧ 2021-09-30 12:47:29
最佳解答
CREATE TABLE TableA(
month varchar(20),	
useMins int(20),	
sumCase int(20),	
sumPer int(20),
sumChat int(20),
sumRating int(20)
);

INSERT INTO TableA (month,useMins,sumCase,sumPer,sumChat,sumRating)
VALUES 
('6',42,38,2,200,798),
('7',0,7,1,2,5);
SELECT name,
SUM(CASE WHEN month='1' THEN amount ELSE 0 END) AS '01',
SUM(CASE WHEN month='2' THEN amount ELSE 0 END) AS '02',
SUM(CASE WHEN month='3' THEN amount ELSE 0 END) AS '03',
SUM(CASE WHEN month='4' THEN amount ELSE 0 END) AS '04',
SUM(CASE WHEN month='5' THEN amount ELSE 0 END) AS '05',
SUM(CASE WHEN month='6' THEN amount ELSE 0 END) AS '06',
SUM(CASE WHEN month='7' THEN amount ELSE 0 END) AS '07',
SUM(CASE WHEN month='8' THEN amount ELSE 0 END) AS '08',
SUM(CASE WHEN month='9' THEN amount ELSE 0 END) AS '09',
SUM(CASE WHEN month='10' THEN amount ELSE 0 END) AS '10',
SUM(CASE WHEN month='11' THEN amount ELSE 0 END) AS '11',
SUM(CASE WHEN month='12' THEN amount ELSE 0 END) AS '12'
FROM (
SELECT month,'useMins' AS name,useMins AS amount
FROM TableA 
UNION ALL 
SELECT month,'sumCase' AS name,sumCase AS amount
FROM TableA 
UNION ALL
SELECT month,'sumPer' AS name,sumPer AS amount
FROM TableA 
UNION ALL
SELECT month,'sumChat' AS name,sumChat AS amount
FROM TableA 
UNION ALL
SELECT month,'sumRating' AS name,sumRating AS amount
FROM TableA 
) AS Z
GROUP BY name
ORDER BY CASE WHEN name='useMins' THEN 1 
WHEN name='sumCase' THEN 2
WHEN name='sumPer' THEN 3
WHEN name='sumChat' THEN 4 
WHEN name='sumRating' THEN 5 ELSE 10 END

Demo

pppppeter iT邦新手 5 級 ‧ 2021-09-30 14:24:14 檢舉

謝謝 rogeryao:D
我再去試著把 TableA 換成我的複雜語法試試
不過這確實是我想要的樣子(興奮)

rogeryao iT邦大師 1 級 ‧ 2021-09-30 14:32:20 檢舉

【我的複雜語法】可考慮使用 view
請參閱 : SQL Create View

0
海綿寶寶
iT邦大神 1 級 ‧ 2021-09-30 12:13:06

因為併入了七個table還包含了left join 寫進的暫時table

早說嗎,怎麼你不早說

我資料都做好了

https://ithelp.ithome.com.tw/upload/images/20210930/20001787jfeEKb90CO.png

db fiddle

這題我不會,純提供測試資料給真正高手

pppppeter iT邦新手 5 級 ‧ 2021-09-30 14:25:14 檢舉

謝謝你這抹熱心
我是想說 把問題先簡化再丟上來
神人們會比較清楚我要問的方向~

我要發表回答

立即登入回答