分类汇总(复杂一点)

1.按照科目分组做小计,客户分组做总计 id-科目,company-客户

   客户包含多个科目

 e.g:

 select * from (select * from (select mater.ob as ob,mater.id as id,mater.num as num,mater.company as company 

 from
 (select id||company as ob,id,num,company from (
       select  'a' id ,10 as num,'A' as company
       union all
       select  'a' id ,20 as num,'A' as company
       union all
       select  'b' id ,30 as num,'A' as company
       union all
       select  'b' id ,40 as num,'B' as company
       union all
       select  'c' id ,40 as num,'B' as company
       union all
      select  'c' id ,40 as num,'B' as company
     ))mater
union all
  select * from (select * from (select (id||company||'_') ob,null as id,sum(num) as num,company from (
      select  'a' id ,10 as num,'A' as company
      union all
      select  'a' id ,20 as num,'A' as company
      union all
      select  'b' id ,30 as num,'A' as company
      union all
      select  'b' id ,40 as num,'B' as company
      union all
      select  'c' id ,40 as num,'B' as company
      union all
      select  'c' id ,40 as num,'B' as company
  ) group by company,id) order by company) order by ob) groupby

union all
  select * from (select null as ob,company as id,sum(res.num) as num,company||'_' as company from (
   select (id||company||'_') ob,id,sum(num) as num,company from (
     select  'a' id ,10 as num,'A' as company
     union all
     select  'a' id ,20 as num,'A' as company
     union all
     select  'b' id ,30 as num,'A' as company
     union all
     select  'b' id ,40 as num,'B' as company
     union all
     select  'c' id ,40 as num,'B' as company
     union all
   select  'c' id ,40 as num,'B' as company
) group by company,id) res group by res.company) aa) order by company,ob

 

效果:

aA             a            20            A
aA             a            10            A
aA_                         30            A
bA             b            30            A
bA_                         30            A
                 A            60            A_
bB             b            40            B
bB_                         40            B
cB              c            40            B
cB              c            40            B
cB_                          80            B
                 B            120          B_

-----上帝啊,,,头都大了...