db2的rollup和cube函数

浏览数：126 / 时间：2015年06月12日

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

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

db2的rollup和cube函数