首页 > 其他 > 详细

17.7.31

时间:2017-08-19 10:36:09      阅读:200      评论:0      收藏:0      [点我收藏+]

 

有员工的部门名称;

  SQL> select department_name from departments where department_id in(select department_id from employees where department_id is not null);     (in)

  SQL> select department_name from departments d where exists(select department_id from employees where department_id=d.department_id);    (exists  关联子查询 )

  SQL> select distinct d.department_name from employees e,departments d where e.department_id=d.department_id;   (多表连接)

没有员工的部门名称;

  SQL> select department_name from departments where department_id not in(select department_id from employees where department_id is not null);

  SQL> select department_name from departments d where not exists(select department_id from employees where department_id=d.department_id);

所有管理者的姓名;

  SQL> select last_name from employees where employee_id in (select manager_id from employees);

  SQL> select last_name from employees e where exists(select 1 from employees where manager_id=e.employee_id);

所有普通员工的姓名

  SQL> select last_name from employees where employee_id not in (select manager_id from employees where manager_id is not null);

  SQL> select last_name from employees e where not exists(select 1 from employees where manager_id=e.employee_id);

(把不确定的空值排除掉)

 

rownum 只能 <=

top-N查询:

SQL> select last_name, salary from employees where rownum<=3 order by salary desc;

SQL> select * from (select last_name, salary from employees order by salary desc) where rownum<=3;

 

子查询

单行子查询的思路:

SQL> select salary from employees where last_name=‘Feeney‘;

SQL> select last_name from employees where salary>3000;

SQL> select last_name from employees where salary>(select salary from employees where last_name=‘Feeney‘);

多行子查询的思路:

SQL> select distinct department_id from employees where department_id is not null;

SQL> select department_name from departments where department_id in (10, 20,30);

SQL> select department_name from departments where department_id in (select department_id from employees where department_id is not null);

用多表连接改写

select distinct d.department_name

from employees e, departments d

where e.department_id=d.department_id

 

for dept in 1..27

  for emp in 1..107

   查看emp中是否出现deptid

 

练习:

工资大于全公司平均工资的员工姓名。

SQL> select last_name from employees where salary>(select avg(salary) from employees);

Feeney同年入职的员工姓名

select last_name, hire_date

from employees

where extract(year from hire_date)=

(select extract(year from hire_date) from employees where last_name=‘Feeney‘)

and last_name != ‘Feeney‘;

select last_name, hire_date

from employees

where hire_date between

(select to_date(to_char(hire_date, ‘yyyy‘)||‘0101‘, ‘yyyymmdd‘) from employees where last_name=‘Feeney‘)

and

(select to_date(to_char(hire_date, ‘yyyy‘)||‘1231‘, ‘yyyymmdd‘) from employees where last_name=‘Feeney‘)

Seattle工作的所有员工姓名

select last_name

from employees

where department_id in

(select department_id from departments

where location_id=

(select location_id from locations where city=‘Seattle‘));

查找符合下列条件的员工姓名:和Abel在同一个部门,工资比Olson

select last_name from employees

where department_id=

(select department_id from employees where last_name=‘Abel‘)

and salary >

(select salary from employees where last_name=‘Olson‘);

 

配对子查询:

Feeney在同一个部门、做同一职位的员工姓名:

select last_name, department_id, job_id

from employees

where department_id=

(select department_id from employees where last_name=‘Feeney‘)

and job_id=

(select job_id from employees where last_name=‘Feeney‘)

and last_name != ‘Feeney‘;

select last_name, department_id, job_id

from employees

where (department_id, job_id)=

(select department_id, job_id from employees where last_name=‘Feeney‘)

and last_name != ‘Feeney‘;

 

innot innull值的影响:

(把不确定的空值排除掉)

所有管理者的姓名:

SQL> select last_name from employees where employee_id in (select manager_id from employees);

所有普通员工的姓名:

SQL> select last_name from employees where employee_id not in (select manager_id from employees where manager_id is not null);

 

关联子查询:

工资大于所在部门平均工资的员工姓名。

for i in 1..107所有员工

{

  select avg(salary) from employees where department_id=i.department_id

  if i.salary > i所在部门的平均工资

  保留此记录

}

select last_name

from employees outer

where salary >

(select avg(salary) from employees

 where department_id = outer.department_id);

 

select e.last_name, e.salary, asd.avg_sal

from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd

where e.department_id=asd.department_id

and e.salary>asd.avg_sal;

 

exists/not exists查询:

for i in 1..27所有部门

{

  for j in 1..107所有员工

{

  if i.department_id = j.department_id

  保留此记录

  break

}

}

select department_name

from departments outer

where exists

(select 1 from employees where department_id=outer.department_id);

 

select department_name

from departments outer

where not exists

(select 1 from employees where department_id=outer.department_id);

 

练习:

所有管理者的姓名:

for i in 1..107所有员工

{

  for j in 1..107所有员工

{

  if i.employee_id = j.manager_id

  保留此记录

  break

}

}

select last_name

from employees outer

where exists

(select 1 from employees where manager_id=outer.employee_id);

所有普通员工的姓名:

select last_name

from employees outer

where not exists

(select 1 from employees where manager_id=outer.employee_id);

 

子查询和多表连接的转换:

有员工的部门的名称

select department_name

from departments

where department_id in

(select department_id from employees);

 

select department_name

from departments outer

where exists

(select 1 from employees where department_id=outer.department_id);

 

select distinct d.department_name

from employees e, departments d

where e.department_id=d.department_id;

 

练习:

Seattle工作的所有员工姓名(使用子查询和多表连接两种方式)

(在Seattle的部门:

1.Seattle的location_id是多少

2.Location_id下的部门信息

3.在这些部门中的员工:1.哪些部门2.这些部门的员工

select last_name

from employees

where department_id in

(select department_id from departments

where location_id=

(select location_id from locations where city=‘Seattle‘));

 

select e.last_name

from employees e, departments d, locations l

where e.department_id=d.department_id

and d.location_id=l.location_id

and l.city=‘Seattle‘;

 

最大值查询:

SQL> select last_name from employees where salary=(select max(salary) from employees);

 

top-N查询:

SQL> select last_name, salary from employees where rownum<=3 order by salary desc;

SQL> select * from (select last_name, salary from employees order by salary desc) where rownum<=3;

 

分页查询:

SQL> select * from

(select * from

(select * from

(select last_name, salary from employees order by salary desc)

where rownum<=6)

order by salary)

where rownum<=3

order by salary desc;

 

SQL> select last_name, salary

 from (select rownum row_num, v1.*

             from

               (select last_name, salary from employees order by salary desc) v1

         ) v2

 where row_num between 4 and 6;

 

 select last_name, salary

 from (select rownum row_num, v1.*

             from

               (select last_name, salary from employees order by salary desc) v1

             where rownum<=6

         ) v2

 where row_num >= 4;

 

17.7.31

原文:http://www.cnblogs.com/Zhang-x/p/7264119.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!