MySQL数据库——子查询经典案例

#查询工资最低的员工last_name、salary
SELECT last_name,salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees
);

#查询平均工资最低的部门信息
#方式一：
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②查询①结果上的最低平均工资
SELECT MIN(ag)
FROM (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) ag_dep
#③查询哪个部门编号平均工资等于②
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
    SELECT MIN(ag)
    FROM (
        SELECT AVG(salary) ag,department_id
        FROM employees
        GROUP BY department_id
    ) ag_dep
);
#④查询部门信息
SELECT d.*
FROM departments d
WHERE d.`department_id` = (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) = (
        SELECT MIN(ag)
        FROM (
            SELECT AVG(salary) ag,department_id
            FROM employees
            GROUP BY department_id
        ) ag_dep
    )

);

#方式二：
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY 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
    ORDER BY AVG(salary) ASC
    LIMIT 1
);

#其他暂时搁置不看。

98