我們在課堂上得到了一項任務，至少要提供兩種不同的解決方案來尋找沒有任何員工的部門。從下面可以看到，我成功地完成了任務。
另一個解決方案是額外的信用，我喜歡獲得。不幸的是，我想不出第三種解決方案，希望有人能指出我正確的方向。帶有 MINUS 功能的東西會起作用嗎？
以下是我的設定和 2 個作業測驗用例。任何建議和幫助將不勝感激。
CREATE TABLE departments
(
department_id,
department_name
) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 3, 'Sales' FROM DUAL UNION ALL
SELECT 2, 'DBA' FROM DUAL;
CREATE TABLE employees
(
employee_id,
first_name, last_name,
hire_date,
salary,
department_id
) AS
SELECT 1, 'Lisa', 'Saladino', DATE '2001-04-03', 100000, 1 FROM DUAL UNION ALL
SELECT 2, 'Abby', 'Abbott', DATE '2001-04-04', 50000, 1 FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2001-04-05', 60000, 1 FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2001-04-06', 70000,1 FROM DUAL UNION ALL
SELECT 5, 'Vicky', 'Palazzo', DATE '2001-04-07', 88000,2 FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2001-04-08', 110000,1 FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Altman', DATE '2001-04-10', 66666, 1 FROM DUAL UNION ALL
SELECT 8, 'Jill', 'Coralnick', DATE '2001-04-11', 190000, 2 FROM DUAL UNION ALL
SELECT 9, 'Faith', 'Aaron', DATE '2001-04-17', 122000,2 FROM DUAL;
/* departments with no employees */
select
d.department_id,
d.department_name
from
employees e
right join
departments d on e.department_id = d.department_id
group by
d.department_id, d.department_name
having
count(e.employee_id) = 0;
輸出：
DEPARTMENT_ID DEPARTMENT_NAME
--------------------------------
3 Sales
SELECT
d.department_id,
d.department_name
FROM
departments d
WHERE
NOT EXISTS (SELECT * FROM employees e
WHERE d.department_id = e.department_id)
輸出：
DEPARTMENT_ID DEPARTMENT_NAME
--------------------------------
3 Sales
uj5u.com熱心網友回復：
您也可以使用左連接，如下所示：
select
d.department_id,
d.department_name
from departments d left join employees e
on e.department_id=d.department_id
where e.employee_id is null
此外，您可以使用如下子查詢：
select department_id, department_name
from departments
where department_id not in (select department_id from employees)
和minus你一起嘗試：
select department_id, department_name
from departments
minus
select
d.department_id,
d.department_name
from departments d join employees e
on e.department_id=d.department_id
查看演示。
uj5u.com熱心網友回復：
您可以嘗試以下查詢-
SELECT *
FROM departments
WHERE department_id in (SELECT department_id
FROM departments
minus
SELECT DISTINCT department_id
FROM employees
);
演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/530917.html
標籤：sql甲骨文子查询
上一篇：OracleSQL漏掉了