MySQL 子查询

-- 1. 标量子查询 查询所有员工的姓名和薪水，同时显示其所在部门的平均薪水。
SELECT 
    e.name AS employee_name,
    e.salary AS employee_salary,
    (SELECT AVG(salary) FROM employee WHERE department_id = e.department_id) AS department_avg_salary
FROM 
    employee e;
-- 2. 标量子查询 查找每个部门中薪水最高的员工姓名。
SELECT 
    e.name, 
    e.department_id, 
    e.salary AS highest_salary
FROM 
    employee e
WHERE 
    e.salary = (
        SELECT MAX(salary)
        FROM employee
        WHERE department_id = e.department_id
    );
-- 3. 标量查询 查找入职日期最早的员工的详细信息（包括姓名、地址、薪水、入职日期等）。
SELECT *
FROM employee
WHERE (hire_date) = (
    SELECT MIN(hire_date)
    FROM employee
);