首页
登录 | 注册

oracle连接和层次查询

--内连接
SELECT t.employee_id, t.department_id, t1.department_name, t2.job_title
  FROM EMPLOYEES t
  JOIN departments t1
    ON (t.department_id = t1.department_id)
  JOIN jobs t2
    ON (t.job_id = t2.job_id)
 where t.employee_id >= 100;
SELECT t.employee_id, t.department_id, t1.department_name, t2.job_title
  FROM EMPLOYEES t
  JOIN departments t1
    ON (t.department_id = t1.department_id)
  JOIN jobs t2
    ON (t.job_id = t2.job_id)
 and t.employee_id >= 100;
 
--自连接
select t.employee_id,t.first_name,t1.employee_id,t1.first_name from employees t
join employees t1
on (t.manager_id=t1.employee_id);

--左外连接
select e.last_name,e.department_id,d.department_name
from employees e left outer join departments d
on (e.department_id=d.department_id);
--右外连接
select e.last_name,d.department_id,d.department_name
from employees e right outer join departments d
on (e.department_id=d.department_id); 
--全外连接
select e.last_name,d.department_id,d.department_name
from employees e full outer join departments d
on (e.department_id=d.department_id);
--交叉连接
select e.last_name,d.department_id,d.department_name
from employees e cross join departments d;
--INTERSECT 显示共有的,消除重复的
SELECT employee_id, job_id
  FROM employees
INTERSECT
SELECT employee_id, job_id FROM job_history;
--MINUS 返回第一个查询有但第二个查询中不存在的所有唯一行记录
SELECT employee_id, job_id
  FROM employees
MINUS
SELECT employee_id, job_id FROM job_history;
--层次查询,自底到顶
select t.employee_id,t.last_name,t.job_id,t.manager_id,level from employees t
start with t.employee_id=101
connect by prior t.manager_id=t.employee_id;
--层次查询,自顶到底
select t.employee_id,t.last_name,t.job_id,t.manager_id,level from employees t
start with t.employee_id=101
connect by prior t.employee_id=t.manager_id;

相关文章

  • 作者: itzealot  随着 Microsoft 的 .NET 框架的逐渐流行,许多开发人员迫切想了解关于将 .NET 应用程序与 Oracle 集成的最好的方式的信息 -- 不仅在基本的连通性方面,还包括与使用 Visual Stud ...
  • MySQL 5.6关闭DNS查询 MySQL服务器的日志记录中看到如下的警告: 2015-11-09 08:37:02 1489 [Warning] IP address '104.223.72.XXX' has been resolved ...
  • 在Oracle数据库上构建.NET应用程序(2)
    作者:Oracle中国        文章来源:John Paul Cook       添加引用 因为我们的工程必须与 Oracle 数据库连接,因此必须添加一个到包含我们选择的数据供应程序的 dll 的引用. 在 Solution Ex ...
  • 分享个JAVA学习路线[新手可以参考看下]觉得不好的,大家可以提出来我补充. 第一阶段 技术名称 技术内容 J2SE (java基础部分) java开发前奏 计算机基本原理,Java语言发展简史以及开发环境的搭建,体验Java程序的开发,环 ...
  • 链接Oracle11g  rac jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.24.227.35)(PORT = 1521))(CONNECT_ ...
  • Oracle 12C Sharding部署和测试
    Oracle 12C Sharding部署和测试 日期:2019-02-26 作者:陈举超   一:环境说明 OS:CentOS Linux release 7.5 DB:Oracle 12.2.0.1.0 GSMOCI:2.2.1 本次使 ...

2020 unjeep.com webmaster#unjeep.com
12 q. 0.013 s.
苏ICP备12049786号-20