--在 FROM 子句中使用子查询
2. 返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
[方式一]
select last_name,department_id,salary,
(select avg(salary)from employees e3
where e1.department_id = e3.department_id
group by department_id) avg_salary
from employees e1
where salary >
(select avg(salary)
from employees e2
where e1.department_id = e2.department_id
--group by department_id
)
[方式二]
SELECT a.last_name, a.salary,
a.department_id, b.salavg
FROM employees a, (SELECT department_id,
AVG(salary) salavg
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary > b.salavg;
--单列子查询表达式
- Oracle8i 只在下列情况下可以使用, 例如:
- SELECT 语句 (FROM 和 WHERE 子句)
- INSERT 语句中的VALUES列表中
- Oracle9i中单列子查询表达式可在下列情况下使用:
- DECODE 和 CASE
- SELECT 中除 GROUP BY 子句以外的所有子句中
3. 显式员工的employee_id,last_name和location。其中,若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’。
SELECT employee_id, last_name,
(CASE department_id
WHEN (SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;
4. 查询员工的employee_id,last_name,要求按照员工的department_name排序
SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);
--相关子查询
本教程由尚硅谷教育大数据研究院出品,如需转载请注明来源。