数据库

sql 分组查询语句练习

位置:首页 > 数据库 > SQL Server,2017-10-06 17:40
组函数处理多行返回一行( T )组函数不计算空值( T )where子句在分组之前对检索进行过滤 ( T )查询公司员工工资的最大值,最小值,平均值,总和sel
  1. 组函数处理多行返回一行( T )
  2. 组函数不计算空值( T )
  3. where子句在分组之前对检索进行过滤 ( T )
  4. 查询公司员工工资的最大值,最小值,平均值,总和
select max(salary),min(salary),avg(salary),sum(salary) from employees;
  1. 查询各job_id的员工工资的最大值,最小值,平均值,总和
select max(salary),min(salary),avg(salary),sum(salary) from employees group by job_id;
  1. 选择具有各个job_id的员工人数
select job_id,count(job_id) from employees group by job_id;
  1. 查询员工最高工资和最低工资的差距(DIFFERENCE)
select max(salary)-min(salary) diffrence from employees;
  1. 查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
select manager_id,min(salary) from employees where manager_id is not null having min(salary) >=6000 group by manager_id;
  1. 查询所有部门的名字,location_id,员工数量和工资平均值
select d.department_name,d.location_id,count(employee_id),avg(salary) from employees e,departments d 
where e.department_id=d.department_id group by d.department_name,d.location_id;
  1. 查询公司的人数,以及在1995-1998年之间,每年雇用的人数,结果类似下面的格式
total 1995 1996 1997 1998
30 3 4 6 7
select count(employee_id) total,sum(decode(to_char(hire_date,'YYYY'),1995,1,0))"1995",
sum(decode(to_char(hire_date,'YYYY'),1996,1,0))"1996",
sum(decode(to_char(hire_date,'YYYY'),1997,1,0))"1997",
sum(decode(to_char(hire_date,'YYYY'),1998,1,0))"1998"
from employees;

TAGS:sql 分组查询

猜你喜欢

NewHot