mysql——子查询经典案例

子查询经典案例

1. 查询工资最低的员工信息: last_name, salary

SELECT last_name,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);

2. 查询平均工资最低的部门信息

SELECT *
FROM departments
WHERE department_id=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) ASC
    LIMIT 1
);

SELECT *
FROM departments
WHERE department_id=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary)=(
        SELECT MIN(avgs)
        FROM (
            SELECT AVG(salary) avgs,department_id
            FROM employees
            GROUP BY department_id
        ) avg_table
    )
);

3. 查询平均工资最低的部门信息和该部门的平均工资

SELECT d.*,AVG(salary)
FROM departments d
INNER JOIN employees e ON d.department_id=e.department_id
WHERE