oracle总结2

1.分组统计

组函数忽略列中的空值

group by select_list
having ...  ----having用来对分组设置过滤条件

distinct 　唯一显示，表示值不重复
---count() 求行数
---avg()   求平均值
---sum()　　求和
---max()　　求最大值
---min()　　求最小值


---count()
select count(*) counts from employees;

---avg()
select round(avg(salary),2) avg_salary  from employees;

---sum()
select sum(salary) sum_salary  from employees;

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

---group by ,having
select nvl(department_id,0) department_id,sum(salary) sum_salary
   from employees group by nvl(department_id,0) having sum(salary) > 100000;
   
---distinct
 select distinct max_salary from jobs
 order by max_salary desc;


#组函数忽略列中的空值

select round(avg(COMMISSION_PCT),2) avg_pct from employees;
 
select round(avg(nvl(COMMISSION_PCT,0)),2) avg_pct from employees;
 
select round(avg(COMMISSION_PCT),2) avg_pct from employees where COMMISSION_PCT is not null;


2.子查询

单行子查询：只返回一行
多行子查询：返回多行数据

---单行子查询
查询雇员编号为141的last_name,job_id

 
 select job_id,last_name from employees where job_id=(
  select job_id from employees where employee_id =141);

---多行子查询
in/any/all


in：等于列表中任何成员
any:比较子查询返回的每个值
all:比较子查询返回的全部值

//每个部门最少薪水的雇员编号和雇员

select  employee_id,last_name  from employees
where salary in
(select min(salary) from employees group by department_id);

---any
select  employee_id,last_name,salary  from employees where salary < any(

select min(salary) from employees where job_id='IT_PROG');


3.rownum(伪列)

rownum并非在表中存在的真正的列，操作rownum只能是select,rownum用来标识结果集中每一条记录的顺序，对于结果集中第一条记录，其rownum为1.

---显示10行记录
select employee_id,last_name,rownum from employees where rownum <=10;