大橙子网站建设,新征程启航

为企业提供网站建设、域名注册、服务器等服务

SQL基础之组函数(九)

组函数:

创新互联成都企业网站建设服务,提供网站建设、成都网站制作网站开发,网站定制,建网站,网站搭建,网站设计,响应式网站建设,网页设计师打造企业风格网站,提供周到的售前咨询和贴心的售后服务。欢迎咨询做网站需要多少钱:18982081108

– 类型和语法

– 使用 AVG, SUM, MIN, MAX, COUNT

– 组函数使用 DISTINCT 关键字

– 组函数中NULL 值

分组函数:作用于一组数据,并对一组数据返回一个值

组函数类型

  • AVG 平均值

  • COUNT 统计值

  • MAX 最大值

  • MIN 最小值

  • SUM 合计

  • STDDEV 标准差

  • VARIANCE 方差

组函数语法:

select group_function(column), ... from table [where condition] [order by column];

使用 AVG 和 和 SUM 函数

可以对数值型数据使用 AVG 和 SUM 函数

1、查询job_id为REP的 平均工资,最高工资,工资总和

select avg(salary),max(salary),min(salary),sum(salary) from employees where job_id like '%REP%';

SQL 基础之组函数(九)

使用 MIN 和 和 MAX 

可以对数值型、字符型和日期型使用 MIN 和 MAX 函数

2、查询入职时最短和最长时间

select min(hire_date),max(hire_date) from employees;

SQL 基础之组函数(九)

使用 COUNT 

1、统计一下department_id 为50的部门有多少人

select count(*) from employees where department_id =50;

SQL 基础之组函数(九)

2、如果有空值不会被计算进去

select count(commission_pct)  from employees where department_id=80;

SQL 基础之组函数(九)

3、显示 EMPLOYEES 表中不同的部门数

select count(distinct department_id) from employees;

SQL 基础之组函数(九)

组函数忽略空值

1、统计一下提成

select avg(commission_pct) from employees;

SQL 基础之组函数(九)

2、将所有的人都统计进来

select avg(nvl(commission_pct,0)) from employees;

SQL 基础之组函数(九)

分组数据:GROUP BY

  • 可以使用GROUP BY 子句将表中的数据分成若干组.

  • group by 后面不能使用列别名,select 后面有限制.

1、求出EMPLOYEES中各个部门的平均工资

select department_id,avg(salary) from employees group by department_id order by department_id;

SQL 基础之组函数(九)

2、包含在 GROUP BY 子句中的列不必包含在SELECT 列表中。

select sum(salary) from employees group by job_id;

SQL 基础之组函数(九)

3、进行多组分列,按照部门和工作进行分组得到分组后工资的和

select department_id,job_id,sum(salary) from employees group by department_id,job_id order by department_id;

SQL 基础之组函数(九)

非法使用组函数

SELECT 列表中的列或表达式,未包含在组函数中的列,都必须包含于GROUP BY 子句中

错误:

select department_id, count(last_name) from employees;

select department_id, job_id, count(last_name) from employees group by department_id;

也就是说必须把department_id 和job_id 加入到group by 中

正确:

select department_id, count(last_name) from employees group by department_id;

select department_id, job_id, count(last_name) from employees group by department_id,job_id;

  • 不能使用 WHERE 子句来过滤组

  • 可以使用 HAVING 子句来过滤组

错误:

select department_id, avg(salary) from employees where avg(salary) > 8000 group by department_id;

过滤分组:HAVING  子句

使用 HAVING 子句过滤分组条件:

  • 行已经被分组。

  • 使用了组函数。

  • 满足HAVING 子句中条件的分组将被显示

语法:

select column, group_function from table [where condition]

[group by group_by_expression]

[having group_condition]

[order by column];

1、每个部门的最高薪水大于$10,000

select department_id,max(salary) from employees group by department_id having max(salary)>10000;

SQL 基础之组函数(九)

2.查找不是REP工作的工资总和大于13000的,并按照sum salary排序。

select job_id ,sum(salary) from employees where job_id not like '%REP%' group by job_id having sum(salary) > 13000

order by sum(salary);

SQL 基础之组函数(九)

嵌套组函数

按照部门分类显示平均工资的最大值:

select  max(avg(salary)) from employees group by department_id;

SQL 基础之组函数(九)

但是嵌套组函数好像不能在添加新的列了

练习题:

1、找出所有员工工资的最大值,最小值和以及平均值。并以此将各列的别名修改

为”Maximum”,”Minimum”,”Sum”,”Average”。并且要求将结果进行四舍五入。

select round(max(salary) ,0) "Maxinmum", round(min(salary),0) Minimum, round(sum(salary),0) Sum ,round(avg(salary),0) Average from employees;

SQL 基础之组函数(九)

2、以 job_id 进行分组,查看每个工种的工资的最大值,最小值,和,以及平均值

select  job_id,max(salary),min(salary),sum(salary),avg(salary) from employees group by job_id;

SQL 基础之组函数(九)

3、写一个查询语句,统计每一个工种的员工数

select job_id,count(employee_id)from employees group by job_id;

SQL 基础之组函数(九)

4、让HR部门的同事可以输入一个工种,然后 SQL 返回该工种的员工数量。

select job_id,count(*) from employees where job_id like '&job_title' group by job_id;

SQL 基础之组函数(九)

5、直接显示出所有经理的总人数。并将该列标记为"Number of Managers".提示:使用 MANAGER_ID 这一列来确定经理的数量

select count(distinct manager_id) "Number of Managers"  from employees;

SQL 基础之组函数(九)

6、查出薪水最高的和薪水最低的差值,并将该列标记为 DIFFERENCE

select max(salary) - min(salary) "DIFFERENCE"  from employees;

SQL 基础之组函数(九)

7、请查询出每个经理手下工资最低的员工,那些没有经理的员工需要排除,并且需要排除那些最低薪水

小于等于6000 组。最后将结构根据薪水以降序排列。

select manager_id,min(salary)

from employees

where manager_id is not null

group by manager_id

having min(salary) > 6000

order by min(salary) desc;

SQL 基础之组函数(九)

8、请编写一条 SQL 语句,查看员工的总数,以及在 1996,1997,1998,1999 这几年被雇佣的员工数量,并为各列取合适的别名。

select count(*) total,

sum(decode(to_char(hire_date, 'fm YYYY'),1999,1,0)) "1999",

sum(decode(to_char(hire_date, 'fm YYYY'),1998,1,0)) "1998",

sum(decode(to_char(hire_date, 'fm YYYY'),1997,1,0)) "1997",

sum(decode(to_char(hire_date, 'fm YYYY'),1996,1,0)) "1996"

from employees;

SQL 基础之组函数(九)

9、请通过一个矩阵显示出所需要的结果,要求是根据部门编号(20,50,80,90)算出对应的工种的工

资,以及该工种的工资总和,对于部门号是 20,50,80,90 这几列来说,请给出一个合适的别名。

select job_id "job",

sum(decode(department_id,20,salary)) "dept 20",

sum(decode(department_id,50,salary)) "dept 50",

sum(decode(department_id,80,salary)) "dept 80",

sum(decode(department_id,90,salary)) "dept 90",

sum(salary) "total"

from employees

group by job_id;

SQL 基础之组函数(九)


分享名称:SQL基础之组函数(九)
分享地址:http://dzwzjz.com/article/phoccp.html
在线咨询
服务热线
服务热线:028-86922220
TOP