mysql - 将字段值传递到子查询中

MySQL是一种常用的关系型数据库管理系统，可以进行各种复杂的数据操作和查询。在MySQL中，有时我们需要将字段的值传递到子查询中进行进一步的处理和筛选。本文将介绍如何使用MySQL将字段值传递到子查询中，并提供相关的案例代码进行演示。

SELECT *
FROM orders
WHERE customer_id = (
  SELECT customer_id
  FROM customers
  WHERE customer_name = 'John'
);

SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = employees.department_id
);

sql
-- 创建orders表
CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE
);
-- 创建customers表
CREATE TABLE customers (
  customer_id INT,
  customer_name VARCHAR(100)
);
-- 插入示例数据
INSERT INTO orders (order_id, customer_id, order_date)
VALUES
  (1, 1, '2022-01-01'),
  (2, 2, '2022-01-02'),
  (3, 1, '2022-01-03');
INSERT INTO customers (customer_id, customer_name)
VALUES
  (1, 'John'),
  (2, 'Mary');

sql
-- 查询名为"John"的客户的所有订单信息
SELECT *
FROM orders
WHERE customer_id = (
  SELECT customer_id
  FROM customers
  WHERE customer_name = 'John'
);

sql
-- 创建employees表
CREATE TABLE employees (
  employee_id INT,
  employee_name VARCHAR(100),
  salary DECIMAL(10, 2),
  department_id INT
);
-- 插入示例数据
INSERT INTO employees (employee_id, employee_name, salary, department_id)
VALUES
  (1, 'John', 5000, 1),
  (2, 'Mary', 6000, 1),
  (3, 'Tom', 7000, 2),
  (4, 'Jerry', 5500, 2);
-- 查询每个部门的平均工资，并找出工资高于部门平均工资的员工
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = employees.department_id
);