找工作了，面试官问我一个查询问题，给我一张表
ID 部门 工资
1 a 1000
2 a 2000
3 a 3000
4 a 1000
5 b 2000
6 b 3000
7 b 2000
8 b 3000
让我按下面格式查询各部门不同工资各有多少人
部门 1000 2000 3000
------解决方案--------------------
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;
------解决方案--------------------
oracle11g下也可以
with test as
(
select 1 as id,'a' as dept,1000 as salary from dual
union all