MySQL中with rollup的用法及說明

2022-10-14 14:04:27

MySQL with rollup的用法

當需要對資料庫資料進行分類統計的時候，往往會用上groupby進行分組。

而在groupby後面還可以加入withcube和withrollup等關鍵字對資料進行彙總。

mysql檔案

with rollup概述

with在sql語句中定義在group by之後。當需要對資料庫資料進行分類統計的時候，往往會用上groupby進行分組。

而在groupby後面還可以加入withcube和withrollup等關鍵字對資料進行彙總。

不過這個cube在mysql中並不適用。

應用範例

現在有這樣一張學生表，裡面的資料如下所示。

如果想對根據學生，對科目，分數求和，可以這樣寫。

如果想在這個的基礎上，求出學生的總分數，應該怎麼做。

使用 WITH ROLLUP，此函數是對聚合函數進行求和，注意 with rollup是對 group by 後的第一個欄位，進行分組求和。

ORDER BY不能在rollup中使用，兩者為互斥關鍵字，如果使用，會丟擲以下錯誤：Error Code:1221. Incorrect usage of CUBE/ROLLUP and ORDER BY。mysql5.7中是不支援的，在8.0以後支援。

相信大家已經知道如何使用with rollup了，這個就是在group by分組之後，再次對聚合函數進行求和。

MySQL with rollup 聚合函數類似oracle cube操作

作用

在分組統計資料的基礎上再進行統計彙總

題目範例

根據題目意思需要求每個價格區間，時間的mark=0的和/mark=1的和

最後還有求個所有價格區間按月份的mark=0的和/mark=1的和

SELECT
	PriceBand,
	YearMonth,
	sum( CASE Mark WHEN 0 THEN VALUE END ) / sum( CASE Mark WHEN 1 THEN VALUE END ) 
FROM
	test 
GROUP BY
	YearMonth,
	PriceBand UNION ALL
SELECT
	'Alll priceBand',
	YearMonth,
	sum( CASE Mark WHEN 0 THEN VALUE END ) / sum( CASE Mark WHEN 1 THEN VALUE END ) 
FROM
	test 
GROUP BY
	YearMonth

在不知道mysql有這種聚合函數的情況我所想到的只能靠這樣拼接來實現

使用rollup後

SELECT
	ifnull( PriceBand, 'All priceBand' ),
	YearMonth,
	sum( CASE Mark WHEN 0 THEN VALUE END ) / sum( CASE Mark WHEN 1 THEN VALUE END ) 
FROM
	test 
GROUP BY
	YearMonth,
	PriceBand WITH ROLLUP 
HAVING
	YearMonth IS NOT NULL