oracle：条展子查询两个表

Question

使用Oracle数据库，我有两个表：

Employees:

Employee_id | Number(6,0)
Last_name   | Varchar2(20)
Hire_date   | Date
Deparment_id| Number(4,0)

Job_history:

Employee_id  | Number(6,0)
Start_date   | Date
Deparment_id | Number(4,0)

我应该找到 - 使用with -sause-当前在同一部门工作的所有员工开始工作（Hire_date = start_date和同一dectment_id）。我使用子查询中的加入很容易获得正确的结果：（

SELECT DISTINCT e.employee_id, e.last_name, e.hire_date, 
e.department_id as current_dep, j.department_id as prev_dep
FROM hr.employees e 
JOIN (SELECT employee_id, department_id, end_date, start_date 
      FROM hr.job_history ) j
ON e.employee_id = j.employee_id
WHERE e.department_id = j.department_id;

右）输出：

不幸的是，我不确定如何管理两个不同的表（大多数示例i在网络上发现的只是一张表）

 --best try until now--

With find_emp as (SELECT hire_date, department_id 
                    FROM hr.employees)
SELECT e.employee_id, e.last_name, e.department_id as curr_dep
FROM HR.employees e
WHERE e.hire_date IN (SELECT j.start_date
                      FROM hr.job_history j
                      JOIN hr.employees e
                      ON e.employee_id = j.employee_id);

（错误）输出：

我在做什么错？由于我是SQL的新手，因此我将感谢所有提示。非常感谢您。

Answer 1

带有子句的SQL创建“虚拟”表，您可以在该子句下的以后查询中引用。这些表中存在记忆中查询的寿命。它们有点像视图。

当您建立一个称为find_emp的虚拟表时，您的示例无法正常工作，但是您没有使用它。

一个示例

WITH subquery AS (
SELECT col1 , col2, col3 
FROM table1
WHERE col4=condition1
)

SELECT * FROM subquery;

希望有所帮助。

Answer 2

如果您正在寻找一名具有相同工作的员工，这意味着该员工的Job_history表中只有一个条目。

因此，您可以做这样的事情。

CREATE table dept  (department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'MARKETING' FROM DUAL UNION ALL
SELECT 3, 'SALES'  FROM DUAL;

CREATE TABLE employees (employee_id, first_name, last_name, department_id, sal) AS
SELECT 1, 'Alice', 'Abbot', 1, 100000 FROM DUAL UNION ALL
SELECT 2, 'Beryl', 'Baron', 1, 50000 FROM DUAL;

CREATE table job_history  (employee_id,
department_id) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 2, 3   FROM DUAL;

WITH rws AS
(
  SELECT 
   e.employee_id,
   e.first_name, 
   e.last_name,
   e.department_id,
   d.department_name
 FROM EMPLOYEES e join dept d on e.department_id = d.department_id
WHERE EMPLOYEE_ID IN
  (SELECT EMPLOYEE_ID FROM JOB_HISTORY GROUP BY EMPLOYEE_ID  HAVING COUNT(EMPLOYEE_ID) =1)
)
SELECT 
   employee_id,
   first_name, 
   last_name,
   department_id,
   department_name
 FROM rws;

EMPLOYEE_ID    FIRST_NAME    LAST_NAME    DEPARTMENT_ID    DEPARTMENT_NAME
1    Alice    Abbot    1    IT

Answer 3

应该是一条线：（

WITH start_dept AS
(
  SELECT emp.employee_id, dept.deparment_id AS prev_dep
    FROM employees   emp
       , job_history dept
   WHERE emp.employee_id = dept.employee_id
     AND emp.hire_date   = dept.start_date
)
SELECT e.employee_id, e.last_name, e.hire_date, e.deparment_id AS current_dep, sd.prev_dep
  FROM employees  e
     , start_dept sd
 WHERE e.employee_id  = sd.employee_id
   AND e.deparment_id = sd.prev_dep;

假设员工。deparment_id是当前的部门和员工。hire_date与job_history中的start_date匹配）