mysql 子查詢高級經典例題

查詢部門平均工資是最低部門的信息
法1：先找到平均工資和最低平均工資相等的部門編號，然后再作為篩選條件匹配部門表

SELECT d.*
FROM departments d
WHERE d.department_id=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) =
    (
        SELECT MIN(a)
        FROM(
            SELECT AVG(salary) a,department_id
            FROM employees
            GROUP BY department_id
                 )b
    )
)

或

法2：通過排序然后LIMIT直接找到工資最低的部門標號，然后匹配部門表

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

查詢平均工資最低的部門信息和該部門的平均工資
法：將department表和帶有最低平均工資的表連接起來，再查詢

SELECT d.*,a
FROM departments d
INNER JOIN(
    SELECT AVG(salary) a,department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) ASC
    LIMIT 1
) b
ON d.department_id=b.department_id;

查詢平均工資最高的job信息

SELECT *
FROM jobs
WHERE jobs.`job_id`=(
    SELECT job_id
    FROM employees e
    GROUP BY e.job_id
    ORDER BY AVG(salary) DESC
    LIMIT 1
);

查詢平均工資高于公司平均工資的部門有些
法：查找平均工資高于公司平均工資的表，然后和部門表連接

    SELECT department_name
FROM departments d
INNER JOIN(
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary)>(
        SELECT AVG(salary)
        FROM employees
    )
) a
WHERE d.department_id=a.department_id;

查詢出公司中所有manager的詳細信息在employees表中

SELECT *
FROM employees
WHERE employee_id IN(
    SELECT manager_id
    FROM employees
);

查詢各個部門中最高工資中的那個部門的最低工資是多少

SELECT MIN(e.salary)
FROM employees e
WHERE e.department_id=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY MAX(salary) DESC
    LIMIT 1
)

查詢平均工資最高的部門的manager的詳細信息：last_name,department_id,email,salary

SELECT e.last_name,e.department_id,e.email,e.salary
FROM employees e
INNER JOIN departments d
ON d.manager_id=e.employee_id
WHERE d.department_id=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) DESC
    LIMIT 1
)