oracle统计
ROLLUP 使用 小计 合计SELECT NVL(L.CHNLNAME, '合计') AS 栏目名称,
NVL(SUM(DECODE(T.CRUSER,'system',1)), 0) AS采集量,
NVL(SUM(DECODE(T.DOCSTATUS,10,1)),0) AS发布量
FROMWCMCHNLDOCC ,WCMDOCUMENTT , WCMCHANNEL L, WCMWEBSITE E
WHERET.DOCID = C.DOCID
ANDC.CHNLID = L.CHANNELID
ANDT.SITEID = E.SITEID
GROUP BY ROLLUP ( L.CHNLNAME )
groupingset使用
SELECT
WCMGRPUSER.USERIDASUSERID,
NVL(WCMDOCUMENT.OPERUSER , '合计')ASPUBUSER,
NVL(SUM(DECODE(WCMCHANNEL.SITEID, 4 ,1)),0)AS NJ,
SUM(1) ASAllDOCCOUNT
FROMWCMCHANNEL, WCMDOCUMENT , WCMCHNLDOC ,WCMGRPUSER ,WCMUSER
WHERE (WCMDOCUMENT.DOCID = WCMCHNLDOC.DOCID
ANDWCMCHNLDOC.CHNLID = WCMCHANNEL.CHANNELID
ANDWCMGRPUSER.USERID = WCMUSER.USERID
ANDWCMUSER.USERNAME= WCMDOCUMENT.OPERUSER
ANDWCMCHNLDOC.DOCSTATUS > 0
ANDWCMGRPUSER.GROUPID = 3
AND WCMCHNLDOC.CRTIME >= to_date('2010-02-20 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND WCMCHNLDOC.CRTIME <= to_date('2011-02-24 23:59:59','YYYY-MM-DD HH24:MI:SS')
)
GROUP BYgrouping sets(( WCMDOCUMENT.OPERUSER , WCMGRPUSER.USERID ) ,null)
ORDER BY WCMDOCUMENT.OPERUSER ASC
页:
[1]