db2的rollup和cube函数_动视

db2的rollup和cube函数

2020-11-09 14:46:28 责编:小采

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

下载本文

显示全文

全部频道