oracle子查询的设置

原创

g201909 2024-01-05 09:08:57 ©著作权

©著作权归作者所有：来自51CTO博客作者g201909的原创作品，请联系作者获取转载授权，否则将追究法律责任

在Oracle中，子查询是一种嵌套在主查询中的查询语句，可以用于从内部查询中获取数据，然后将其传递给外部查询。子查询通常用于过滤、计算或检索与主查询相关的数据。以下是一些使用子查询的基本示例：

1. 简单的子查询：

-- 获取部门名为'IT'的员工信息
SELECT employee_id, employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');

2. 子查询中的聚合函数：

-- 获取平均工资高于公司平均工资的员工信息
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

3. EXISTS 子查询：

-- 获取至少有一个订单的客户信息
SELECT customer_id, customer_name
FROM customers
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id);

4. IN 子查询：

-- 获取在指定部门工作的员工信息
SELECT employee_id, employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

5. 使用多个列的子查询：

-- 获取与同一部门和职务相同的员工信息
SELECT employee_id, employee_name
FROM employees
WHERE (department_id, job_id) IN (SELECT department_id, job_id FROM employees WHERE employee_id = 101);

6. 使用子查询进行 UPDATE：

-- 将所有员工的工资增加10%
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR');