SELECT e.last_name, e.hire_date
FROM employees e
JOIN employees
ON (e.last_name = 'HUNOLD')
我應該使用（ORACLE LIVE SQL）顯示與HUNOLD在同一部門作業的任何員工的姓氏和雇用日期，但卡住了，有人可以幫忙嗎？
部門表：
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury - 1700
130 Corporate Tax - 1700
140 Control And Credit - 1700
150 Shareholder Services - 1700
160 Benefits - 1700
170 Manufacturing - 1700
180 Construction - 1700
190 Contracting - 1700
200 Operations - 1700
210 IT Support - 1700
220 NOC - 1700
230 IT Helpdesk - 1700
240 Government Sales - 1700
250 Retail Sales - 1700
260 Recruiting - 1700
270 Payroll - 1700
uj5u.com熱心網友回復：
有一個子查詢回傳 Hunold 的部門。
SELECT e.last_name, e.hire_date
FROM employees e
WHERE e.departmentid in (select e2.departmentid
from employees e2
where e2.last_name = 'HUNOLD')
您還可以JOIN：
SELECT e.last_name, e.hire_date
FROM employees e
JOIN employees e2 ON e.departmentid = e2.departmentid
where e2.last_name = 'HUNOLD'
uj5u.com熱心網友回復：
JOIN如果您使用決議函式，則不需要任何s：
SELECT last_name,
hire_date
FROM (
SELECT last_name,
hire_date,
COUNT(CASE last_name WHEN 'HUNOLD' THEN 1 END) OVER (
PARTITION BY department_id
) AS number_of_hunolds
FROM employees
)
WHERE number_of_hunolds > 0;
如果您確實想使用子查詢，則可以使用EXISTS：
SELECT last_name,
hire_date
FROM employees e
WHERE EXISTS (SELECT 1
FROM employees x
WHERE x.last_name = 'HUNOLD'
AND e.department_id = x.department_id);
或INNER JOIN：
SELECT e.last_name,
e.hire_date
FROM employees e
INNER JOIN employees e2
ON (e.department_id = e2.department_id)
WHERE e2.last_name = 'HUNOLD';
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/327536.html
上一篇：按順序獲取特定的系列值