如何更加简单的实现一下sql语句啊?统计每个分公司业绩的占比
company_id class
公司1 a
公司1 b
公司2 a
公司2 b
公司2 b
公司3 a
公司3 a
公司3 c
公司4 a
公司5 ccompany_id count1 class-a class-a% class-b class-b% class-c class-c%
公司1 2 1 50.00% 1 50.00% 0 0.00%
公司2 3 1 33.33% 2 66.67% 0 0.00%
公司3 3 2 66.67% 0 0.00% 1 33.33%
公司4 1 1 100.00% 0 0.00% 0 0.00%
公司5 1 0 0.00% 0 0.00% 1 100.00%
select a.company_id,count1,
nvl(c1,0) class-a,to_char(trunc(nvl(c1,0)/总数,4)*100)||'%' class-a%,
nvl(c2,0) class-b,to_char(trunc(nvl(c2,0)/总数,4)*100)||'%' class-b%,
nvl(c3,0) class-c,to_char(trunc(nvl(c3,0)/总数,4)*100)||'%' class-c%
from
(
select company_id,count(*) count1
from testtable
group by company_id) a,(
select company_id ,count(*) c1
from testtable
where num=a
group by company_id) b,
(
select company_id ,count(*) c2
from testtable
where num=b
group by company_id) c,
(
select company_id ,count(*) c3
from testtable
where num=c
group by company_id) d)where a.company_id=b.company_id(+) and a.company_id=c.company_id(+) and a.company_id=d.company_id(+)
company_id class
公司1 a
公司1 b
公司2 a
公司2 b
公司2 b
公司3 a
公司3 a
公司3 c
公司4 a
公司5 ccompany_id count1 class-a class-a% class-b class-b% class-c class-c%
公司1 2 1 50.00% 1 50.00% 0 0.00%
公司2 3 1 33.33% 2 66.67% 0 0.00%
公司3 3 2 66.67% 0 0.00% 1 33.33%
公司4 1 1 100.00% 0 0.00% 0 0.00%
公司5 1 0 0.00% 0 0.00% 1 100.00%
select a.company_id,count1,
nvl(c1,0) class-a,to_char(trunc(nvl(c1,0)/总数,4)*100)||'%' class-a%,
nvl(c2,0) class-b,to_char(trunc(nvl(c2,0)/总数,4)*100)||'%' class-b%,
nvl(c3,0) class-c,to_char(trunc(nvl(c3,0)/总数,4)*100)||'%' class-c%
from
(
select company_id,count(*) count1
from testtable
group by company_id) a,(
select company_id ,count(*) c1
from testtable
where num=a
group by company_id) b,
(
select company_id ,count(*) c2
from testtable
where num=b
group by company_id) c,
(
select company_id ,count(*) c3
from testtable
where num=c
group by company_id) d)where a.company_id=b.company_id(+) and a.company_id=c.company_id(+) and a.company_id=d.company_id(+)
这些数据可以直接算的
count(decode(num,a,你要加的字段,null))
from testtable
group by company_id;
不行啊,会报错
count(decode(class,'b',class,null)
from table
group by company_id
round(sum(a)/sum(a+b+c)*100,2)||'%',
sum(b) b,
round(sum(b)/sum(a+b+c)*100,2)||'%',
sum(c) c,
round(sum(c)/sum(a+b+c)*100,2)||'%'
from
(
select company_id,
decode(classid,'a',1,0) a,
decode(classid,'b',1,0) b,
decode(classid,'c',1,0) c
from t
)
group by company_id