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
    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：