Oracle数据库笔记6（聚合函数）

//如何显示所有员工最高工资和最低工资

select max(basicWage) from employee;

select min(basicWage) from employee;

//查询该公司最大工资的人（max计算出相同的值，max返回一个值）

select max(basicWage*12+nvl(subsidy,0)*12) from employee;
select * from employee where basicWage*12+nvl(subsidy,0)*12=785593.44;

//显示出所有员工的平均工资和工资总和（avg不计算空值）方法：select sum(subsidy)/count(*) from employee;

select sum(basicWage+nvl(subsidy,0)) as "每月工资总和",avg(basicWage+nvl(subsidy,0)) as "员工平均工资"  from  employee;

//统计多少人有补贴（count统计的是非空的值）

select count(subsidy) from employee;

//请显示工资最高的员工的名字、工作岗位

SELECT empname,job from employee where (basicWage+subsidy)=(SELECT max(basicWage+subsidy) from employee);

//请显示工资高于平均工资的员工信息

select * from employee where basicWage+subsidy>(select avg(basicWage+subsidy) from employee);

//如何显示每个部门的平均工资和最高工资(排序：order by[asc|desc])

select depno,max(basicWage+nvl(subsidy,0)) "最大工资",avg(basicWage+nvl(subsidy,0))"平均工资" from employee  group by depno order by depno desc;

//显示每个部门每种岗位的平均工资和最低工资

select avg(basicWage+nvl(subsidy,0)),max(basicWage+nvl(subsidy,0)),depno,job from employee group by depno,job ;

//显示部门平均工资低于50000的部门号和他的平均工资

 select avg(basicWage+nvl(subsidy,0)),depno from employee group by depno having avg(basicWage+nvl(subsidy,0))<50000; 

注意：Having与Where的区别

• where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。

• having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

示例1

select 类别, sum(数量) as 数量之和 from A

group by 类别

having sum(数量) > 18

示例2：Having和Where的联合使用方法

select 类别, SUM(数量)from A

where 数量 gt;8

group by 类别

having SUM(数量) gt; 10

sql语句：SELECT SUM(Ccredit),s.SNo from course c,student s,sc
where  sc.CNo=c.CNo AND sc.SNo=s.SNo AND s.SNo='1'
group by s.SNo;