--统计 小计 合计

create table trade_flow (user_code varchar2(12),trans_time date,trans_amount number);

insert into trade_flow values('321000',to_date('2015-09-08 11:50:37','yyyy-mm-dd hh24:mi:ss'),300);
insert into trade_flow values('321000',to_date('2015-09-08 18:47:20','yyyy-mm-dd hh24:mi:ss'),210);
insert into trade_flow values('321002',to_date('2015-09-09 16:41:05','yyyy-mm-dd hh24:mi:ss'),190);
insert into trade_flow values('321002',to_date('2015-09-09 20:11:15','yyyy-mm-dd hh24:mi:ss'),90);
insert into trade_flow values('321000',to_date('2015-09-10 09:00:07','yyyy-mm-dd hh24:mi:ss'),100);
insert into trade_flow values('321000',to_date('2015-09-10 10:30:25','yyyy-mm-dd hh24:mi:ss'),270);
insert into trade_flow values('321000',to_date('2015-09-10 15:06:32','yyyy-mm-dd hh24:mi:ss'),50);
insert into trade_flow values('321002',to_date('2015-09-10 12:33:45','yyyy-mm-dd hh24:mi:ss'),150);
insert into trade_flow values('321002',to_date('2015-09-10 17:23:55','yyyy-mm-dd hh24:mi:ss'),350);

select * from trade_flow order by 2;

select user_code,TRUNC(trans_time),SUM(trans_amount),
grouping(user_code),grouping(TRUNC(trans_time)) from trade_flow 
GROUP BY ROLLUP(user_code,TRUNC(trans_time))
ORDER by user_code,TRUNC(trans_time);

select CASE WHEN (grouping(TRUNC(trans_time))=1 AND grouping(user_code)=1) THEN '总计'
            WHEN grouping(TRUNC(trans_time))=1 
              THEN '小计'                
           ELSE     user_code END user_code,
TRUNC(trans_time),SUM(trans_amount),
grouping(user_code),grouping(TRUNC(trans_time)) from trade_flow 
GROUP BY ROLLUP(user_code,TRUNC(trans_time))
ORDER by TRUNC(trans_time);

select CASE WHEN (grouping(TRUNC(trans_time))=1 AND grouping(user_code)=1) THEN '总计'
            WHEN grouping(TRUNC(trans_time))=1 
              THEN '小计'                
           ELSE     user_code END user_code,
TRUNC(trans_time),SUM(trans_amount),
grouping(user_code),grouping(TRUNC(trans_time)), 
GROUPING_id(user_code,TRUNC(trans_time)),--是否都为空
GROUPING_id(user_code,TRUNC(trans_time))
from trade_flow 
GROUP BY ROLLUP(user_code,TRUNC(trans_time))
ORDER by user_code,TRUNC(trans_time);



select CASE WHEN (grouping(trans_amount)=1 ) THEN '小计'
            --WHEN (grouping(TRUNC(trans_time))=0 AND grouping(user_code)=1) THEN user_code
            /*WHEN grouping(TRUNC(trans_time))=1 
              THEN '小计' */               
           ELSE     user_code END user_code,
TRUNC(trans_time),SUM(trans_amount),
grouping(user_code),grouping(TRUNC(trans_time)),grouping(trans_amount) from trade_flow 
GROUP BY GROUPING SETS((user_code,TRUNC(trans_time),trans_amount), (user_code,TRUNC(trans_time)));


select decode(grouping(trans_amount),1,'小计',0,user_code) user_code,
TRUNC(trans_time),SUM(trans_amount),
grouping(user_code),grouping(TRUNC(trans_time)),grouping(trans_amount),
GROUPING_id(user_code,TRUNC(trans_time)),--是否都为空
GROUPING_id(trans_amount,TRUNC(trans_time))
from trade_flow 
GROUP BY GROUPING SETS((user_code,TRUNC(trans_time),trans_amount), (user_code,TRUNC(trans_time)));


select * from trade_flow order by 2;
select decode(grouping(trans_amount),1,'小计',0,user_code) user_code,
TRUNC(trans_time),SUM(trans_amount),
grouping(user_code),grouping(TRUNC(trans_time)),grouping(trans_amount) from trade_flow 
GROUP BY GROUPING SETS((user_code,TRUNC(trans_time),trans_amount), (user_code,TRUNC(trans_time)));


select t.user_code,t.trans_time,t.trans_amount from trade_flow t WHERE t.user_code=321000;

SELECT * FROM (
       select t.user_code,t.trans_time,t.trans_amount from trade_flow t WHERE t.user_code=321000)
PIVOT (
      MAX(t.trans_time) FOR fieldname IN (
          '300',
          '210',
          '100',
          '270',
          '50'
          )
) ORDER BY 1