MySQL中with?rollup的用法及說明

更新時(shí)間：2022年10月11日 16:09:31 作者：wh柒八九

這篇文章主要介紹了MySQL中with?rollup的用法及說明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

MySQL with rollup的用法

當(dāng)需要對(duì)數(shù)據(jù)庫數(shù)據(jù)進(jìn)行分類統(tǒng)計(jì)的時(shí)候，往往會(huì)用上groupby進(jìn)行分組。

而在groupby后面還可以加入withcube和withrollup等關(guān)鍵字對(duì)數(shù)據(jù)進(jìn)行匯總。

mysql文檔

with rollup概述

with在sql語句中定義在group by之后。當(dāng)需要對(duì)數(shù)據(jù)庫數(shù)據(jù)進(jìn)行分類統(tǒng)計(jì)的時(shí)候，往往會(huì)用上groupby進(jìn)行分組。

而在groupby后面還可以加入withcube和withrollup等關(guān)鍵字對(duì)數(shù)據(jù)進(jìn)行匯總。

不過這個(gè)cube在mysql中并不適用。

應(yīng)用實(shí)例

現(xiàn)在有這樣一張學(xué)生表，里面的數(shù)據(jù)如下所示。

如果想對(duì)根據(jù)學(xué)生，對(duì)科目，分?jǐn)?shù)求和，可以這樣寫。

如果想在這個(gè)的基礎(chǔ)上，求出學(xué)生的總分?jǐn)?shù)，應(yīng)該怎么做。

使用 WITH ROLLUP，此函數(shù)是對(duì)聚合函數(shù)進(jìn)行求和，注意 with rollup是對(duì) group by 后的第一個(gè)字段，進(jìn)行分組求和。

ORDER BY不能在rollup中使用，兩者為互斥關(guān)鍵字，如果使用，會(huì)拋出以下錯(cuò)誤：Error Code:1221. Incorrect usage of CUBE/ROLLUP and ORDER BY。mysql5.7中是不支持的，在8.0以后支持。

相信大家已經(jīng)知道如何使用with rollup了，這個(gè)就是在group by分組之后，再次對(duì)聚合函數(shù)進(jìn)行求和。

MySQL with rollup 聚合函數(shù)類似oracle cube操作

作用

在分組統(tǒng)計(jì)數(shù)據(jù)的基礎(chǔ)上再進(jìn)行統(tǒng)計(jì)匯總

題目示例

根據(jù)題目意思需要求每個(gè)價(jià)格區(qū)間，時(shí)間的mark=0的和/mark=1的和

最后還有求個(gè)所有價(jià)格區(qū)間按月份的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

在不知道m(xù)ysql有這種聚合函數(shù)的情況我所想到的只能靠這樣拼接來實(shí)現(xiàn)

使用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