mysql:查询平均工资最低的部门信息

问题:查询平均工资最低的部门信息

该问题中平均工资涉及employees表,部门信息涉及departments表,
可将该问题按步骤拆分:

  ①查询每个部门的平均工资

  ②查询平均工资最低的部门的平均工资

  ③根据平均工资最低的部门的平均工资去找平均工资最低的部门的部门编号

  ④根据平均工资最低的部门的部门编号去找部门信息

 

具体实现:

  ①查询每个部门的平均工资:  

         SELECT avg( salary ) ag
    FROM departments d
    JOIN employees e
    ON d.department_id = e.department_id
    GROUP BY d.department_id;

  ②查询平均工资最低的部门的平均工资:

         SELECT min( ag )
    FROM (
      SELECT avg( salary ) ag
      FROM departments d
      JOIN employees e
      ON d.department_id = e.department_id
      GROUP BY d.department_id
    ) ag_dep;  这里必须取别名!

  ③根据平均工资最低的部门的平均工资去找平均工资最低的部门的部门编号:

         SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING avg( salary ) = (
      SELECT min( ag )
      FROM (
        SELECT avg( salary ) ag
        FROM departments d
        JOIN employees e
        ON d.department_id = e.department_id
        GROUP BY d.department_id
      ) ag_dep
    );

  ④根据平均工资最低的部门的部门编号去找部门信息:

    SELECT *
    FROM departments
    WHERE department_id =(
      SELECT department_id
      FROM employees
      GROUP BY department_id
      HAVING avg( salary ) = (
        SELECT min( ag )
        FROM (
          SELECT avg( salary ) ag
          FROM departments d
          JOIN employees e
          ON d.department_id = e.department_id
          GROUP BY d.department_id
        ) ag_dep
      )
    );

 

上一篇:Mac下编译安装phpredis


下一篇:揭秘网络AG真人直播视频真实作假内幕