主页 / dba / 问题 / 48822
Accepted
Khalid Hussain
Asked: 2013-08-28 07:26:36 +0800 CST

从 Oracle 的 HR 数据库中获取销售部门的员工历史

  • 772

我目前正在使用带有 Oracle 10g 的“hr”数据库。您可以在此处查看架构图：http: //docs.oracle.com/cd/B13789_01/server.101/b10771/diagrams001.htm#sthref63

我想要的是检索在“销售”部门工作过的所有员工的历史记录。我只为工作示例选择了名字、姓氏和职位，但是我得到了相同的职位，而我应该得到两个不同的职位。

这是我到目前为止的查询：

SELECT a.first_name, a.last_name, b.job_title
FROM employees a, jobs b, job_history c, departments d
WHERE d.department_name = 'Sales'
AND c.employee_id = a.employee_id
AND a.job_id = b.job_id
AND c.department_id = d.department_id;

任何帮助，将不胜感激。JazakAllah khayr。

oracle oracle-10g

3 个回答

  1. SQL 查询介绍，但最好是查找介绍性 SQL 文本

    我假设每个员工都在 job_history 表中，每个员工因此工作分配给一个部门。否则会失去一些员工。

    让我们显示所涉及的表的列：

    select employee_id,first_name,last_name,department_id
from employees;

select department_id, department_name
from departments;

select job_id,employeed_id,department_id
from job_history;

select job_id,job_title
from jobs;

    首先列出所有员工及其部门信息

    select *
from employee e, departments d
where e.department_id=d.department_id;

    现在仅从部门“销售”中的员工中选择行

    select *
from employee e, departments d
where e.department_id=d.department_id
and d.department_name='Sales';

    对于这些员工现在发现工作已经从 job_history

    select *
from employee e, departments d, jobhistory h
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id;

    现在为每个工作找到职位

    select *
from employee e, departments d, jobhistory h, jobs j
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id;

    现在我们只显示我们需要的列

    select e.first_name,e.last_name,j.job_title
from employee e, departments d, jobhistory h, jobs j
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id;

    如果我们想知道他们在哪个部门工作，我们加入另一个部门表以获取这些信息

    select e.first_name,e.last_name,j.job_title,d2.department_name
from employee e, departments d, jobhistory h, jobs j,departments d2
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id
and h.depatment_id=d2.department_id;
    • 1
  2. Best Answer

    我已经弄清楚了原来的问题。我应该从job_history 和jobs 而不是原来的employees 和jobs 表中限制job_id。

    这将是最终的答案：

    SELECT a.employee_id, a.first_name, a.last_name, b.job_title, d.department_name
FROM employees a, jobs b, job_history c, departments d
WHERE d.department_name = 'Sales'
AND c.employee_id = a.employee_id
AND c.job_id = b.job_id
AND c.department_id = d.department_id;
    • 0

  3. 一个更好的答案，使用 ANSI Join 语法，它明显更清晰：

    SELECT e.employee_id, e.first_name, e.last_name, j.job_title, d.department_name
  FROM departments d
       inner join job_history jh
          on d.department_id = jh.department_id
       inner join employees e
          on jh.employee_id = e.employee_id
       inner join jobs j
          on jh.job_id = j.job_id
WHERE d.department_name = 'Sales';

    ANSI 语法有助于防止意外的笛卡尔积，并清楚地区分连接标准和过滤标准。

    • 0

相关问题