分类汇总(复杂一点)

最新推荐文章于 2021-03-11 04:30:17 发布

qwnbc

最新推荐文章于 2021-03-11 04:30:17 发布

阅读量539

点赞数

文章标签： null c

本文链接：https://blog.csdn.net/qwnbc/article/details/4682536

版权

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_

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