with test as
(
select 1 as id,'a' as dept,1000 as salary from dual
union all
select 2 as id,'a' as dept,2000 as salary from dual
union all
select 3 as id,'a' as dept,3000 as salary from dual
union all
select 4 as id,'a' as dept,1000 as salary from dual
union all
select 5 as id,'b' as dept,2000 as salary from dual
union all
select 6 as id,'b' as dept,3000 as salary from dual
union all
select 7 as id,'b' as dept,2000 as salary from dual
union all
select 8 as id,'b' as dept,3000 as salary from dual
)select t.dept,sum(decode(salary,1000,1,0)) as s1000,sum(decode(salary,2000,1,0)) as s2000,sum(decode(salary,3000,1,0)) as s3000 from test t group by t.dept;
(
select 1 as id,'a' as dept,1000 as salary from dual
union all
select 2 as id,'a' as dept,2000 as salary from dual
union all
select 3 as id,'a' as dept,3000 as salary from dual
union all
select 4 as id,'a' as dept,1000 as salary from dual
union all
select 5 as id,'b' as dept,2000 as salary from dual
union all
select 6 as id,'b' as dept,3000 as salary from dual
union all
select 7 as id,'b' as dept,2000 as salary from dual
union all
select 8 as id,'b' as dept,3000 as salary from dual
)
select * from(select t.dept,t.salary,count(1) as cou from test t group by t.dept,t.salary order by t.dept)
pivot (sum(cou) for salary IN('1000','2000','3000'));
同菜鸟 木有测试环境 写了个想法