1.需求：在一个办公系统中，有一个决策分析的功能。有一子模块,是按部门统计各个工资级别的人数、平均工资。

2.涉及到的表：

2.1 UserSalary:用户工资表.

字段为:用户编号（userId),基本工资(salary1)。数据如：sd100301,5000

2.2 SalaryGrade:工资级别表.

字段为:工资级别(grade),级别起点(losal)，级别终点（hisal）。数据如：1,0,1000；2,1000,2000

2.3 UserDeptJob:用户部门职位表。

字段为：userId(用户编号),institutionId（部门编号）,jobId（职位编号）

2.4 Institution:部门表。

字段为：institutionId(部门编号），部门名称(institutionName)

3.SQL:

第一 步：

select max(s.grade) as grade,count(e.userId) as peopleCount,
i.institutionName--,e.salary1 as salary1
from Salarygrade s left join UserSalary e
on e.salary1 between s.losal and hisal
join UserDeptJob udj
on udj.userId=e.userId
join institution i
on i.institutionId=udj.institutionId
where e.salary1>0
group by s.grade,i.institutionName--,e.salary1

http://s14/middle/4f9ce8f3gb5aa330b975d&690

第二部：

select institutionName,
nvl(max(decode(grade,'1',peopleCount)),0) as first, 
nvl(max(decode(grade,'2',peopleCount)),0)   as second ,
nvl(max(decode(grade,'3',peopleCount)),0) as third,
nvl(max(decode(grade,'4',peopleCount)),0) as fouth ,
nvl(max(decode(grade,'5',peopleCount)),0) as fifth --,avg(salary1)
from
(
select max(s.grade) as grade,count(e.userId) as peopleCount,
i.institutionName--,e.salary1 as salary1
from Salarygrade s left join UserSalary e
on e.salary1 between s.losal and hisal
join UserDeptJob udj
on udj.userId=e.userId
join institution i
on i.institutionId=udj.institutionId
where e.salary1>0
group by s.grade,i.institutionName--,e.salary1
)
group by institutionName
http://s6/middle/4f9ce8f3gb5aa3841fb25&690

SQL说明：连接员工工资表和工资基本表，可以统计出员工在各个工资级别的分布；再连接部门表，即可按部门统计工资的级别分布情况。

4.结果：