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
图片1:
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
图片2:
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
图片3: