关于rollup()的问题

Question

本帖最后由 wangwei1340848 于 2009-06-09 18:30:11 编辑

Answer 1

怎么成这个状态了，我是想要Html显示的效果，不是代码，昏！！！

Answer 2

select
case when city_code is null 
               then '合计' else city_code end city_code,
               city,
              ptuser_daily,
              agentuser_daily,
              total_user_daily,
              ptuser_month,
              agentuser_month,
              total_user_month
      
 from(select case
         when m5.city_code is null then
          '合计'
         else
          m5.city_code
       end city_code,
       m5.city,
       m1.pyuser ptuser_daily,
       m2.agentuser agentuser_daily,
       sum(m1.pyuser)+sum(m2.agentuser) total_user_daily,
       m3.ptuser ptuser_month,
       m4.agentuser agentuser_month,
       sum(m3.ptuser) + sum(m4.agentuser) total_user_month  from (
  select
   case when city_code is null 
               then '合计' else city_code end city_code,
               sum(pyuser)pyuser
   from (
  select count(*) pyuser,
     t.city_code 
              
            
        
          from MOBILBABY_business_order_T t
         where substr(t.agent_code, 0, 5) = '10010'
           and t.operate_date <
               to_date(to_char(sysdate - 1, 'yyyymmdd'), 'yyyymmdd')
           and to_char(t.operate_date, 'yyyymmdd') = '20090601'
           
           group by t.city_code
        
        ) group by rollup(city_code))m1,
       
       (
       
       select   
        case   when city_code is null 
               then '合计' else city_code end city_code,
               sum(agentuser)agentuser
               
       from 
       
       (select count(*) agentuser,
          t.city_code  
               
        
          from MOBILBABY_business_order_T t
         where substr(t.agent_code, 0, 5) != '10010'
           and t.operate_date <
               to_date(to_char(sysdate - 1, 'yyyymmdd'), 'yyyymmdd')
           and to_char(t.operate_date, 'yyyymmdd') = '20090601'
           group by t.city_code
        
        
        
        ) group by city_code) m2,
       
       (
       select    case   when city_code is null 
               then '合计' else city_code end city_code,
               
               sum(ptuser)ptuser
       from (
       
       select count(*) ptuser,
      t.city_code
        
          from MOBILBABY_business_order_T t
         where substr(t.agent_code, 0, 5) = '10010'
           and t.operate_date between
               to_date(to_char(sysdate - 1, 'yyyymm'), 'yyyymm') and
               to_date(to_char(add_months(sysdate, 1), 'yyyymm'), 'yyyymm')
           and to_char(t.operate_date, 'yyyymmdd') = '20090601'
           group by t.city_code
        
        )group by rollup(city_code)) m3,
       
       (
       
       select case   when city_code is null 
               then '合计' else city_code end city_code,
                sum(agentuser)agentuser from (
       
       select count(*) agentuser,
        t.city_code 
               
        
          from MOBILBABY_business_order_T t
         where substr(t.agent_code, 0, 5) != '10010'
           and t.operate_date between
               to_date(to_char(sysdate - 1, 'yyyymm'), 'yyyymm') and
               to_date(to_char(add_months(sysdate, 1), 'yyyymm'), 'yyyymm')
           and to_char(t.operate_date, 'yyyymmdd') = '20090601'
           group by t.city_code
        
         ) group by city_code) m4,
       (select t.city_code, t.city
          from MOBILBABY_business_order_T t
         where to_char(t.operate_date, 'yyyymmdd') = '20090601'
         group by t.city_code, t.city) m5 where m5.city_code = m2.city_code(+)
   and m5.city_code = m4.city_code(+)
   and m5.city_code = m1.city_code(+)
   and m5.city_code = m3.city_code(+)
   
   group by  m5.city_code,
   m5.city,
   m1.pyuser,
   m2.agentuser,
   m3.ptuser,
    m4.agentuser
   
   
)
group by  rollup(city_code),
               city,
                ptuser_daily,
              agentuser_daily,
              total_user_daily,
              ptuser_month,
              agentuser_month,
              total_user_month
              
             /* order by city_code */
             
         
          
         
   

Answer 3

你这个合计不是ROLLUP产生的啊
另外你最后的GROUP BY有这么多字段，ROLLUP不一定能符合你要求