mysql中的cube和rollup_db2的rollup和cube函数

rollup的效果就是对group by后面的一个分组列名进行统计。 bankid为第一列 SELECT CASE WHEN grouping (branchid) = 0 THEN branchid ELSE 小计 END as branchid, CASE WHEN grouping (bankid) = 0 THEN bankid ELSE 总计 END as bankid, SUM (balance)FROM

rollup的效果就是对group by后面的一个分组列名进行统计。

bankid为第一列

SELECT

CASE

WHEN grouping (branchid) = 0

THEN branchid

ELSE '小计'

END as branchid,

CASE

WHEN grouping (bankid) = 0

THEN bankid

ELSE '总计'

END as bankid,

SUM (balance)

FROM

ebanksumm

WHERE

workdate = '20140320'

GROUP BY

bankid ,

branchid

with rollup

branchid为第一列

SELECT

CASE

WHEN grouping (branchid) = 0

THEN branchid

ELSE '小计'

END AS branchid ,

CASE

WHEN grouping (bankid) = 0

THEN bankid

ELSE '总计'

END AS bankid ,

SUM (balance)

FROM

ebanksumm

WHERE

workdate = '20140321'

GROUP BY

branchid ,

bankid WITH rollup

cube函数则是对groupby后面的所有列依次进行统计。和rollup的区别就在于要统计的列。rollup只统计一列，cube统计所有列

SELECT

CASE

WHEN grouping (branchid) = 0

THEN branchid

ELSE '小计'

END as branchid,

CASE

WHEN grouping (bankid) = 0

THEN bankid

ELSE '总计'

END as bankid,

SUM (balance)

FROM

ebanksumm

WHERE

workdate = '20140321'

GROUP BY

bankid ,

branchid

with Cube

本文原创发布php中文网，转载请注明出处，感谢您的尊重！