【大数据系列之MySQL】（二十七）：子查询的经典使用案例

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

案例1：查询工资最低的员工信息：last_name，salary（where后标量子查询）

首先查询出最低工资，然后使用该工资进行筛选

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

案例2：查询平均工资最低的部门信息（from后表子查询，where后标量子查询）

先查询出部门的平均工资，然后在查哪个部门的平均工资最低，再查哪个部门的平均工资等于最低工资，最后查询部门信息

SELECT
  * 
FROM
  departments 
WHERE
  department_id =(
  SELECT
    department_id 
  FROM
    ( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) ad 
  WHERE
    avg =(
    SELECT
      min( avg ) 
    FROM
    ( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) ad 
  ));

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

SELECT
  * 
FROM
  (
  SELECT
    * 
  FROM
    departments 
  WHERE
    department_id =(
    SELECT
      department_id 
    FROM
      ( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) ad 
    WHERE
      avg =(
      SELECT
        min( avg ) 
      FROM
        ( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) ad 
      ))) a
  JOIN ( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) b ON a.department_id = b.department_id;

案例4：查询平均工资最高的job信息

先查询出平均工资最高的job_id是什么，然后再去job表中查询

SELECT
  * 
FROM
  jobs 
WHERE
  job_id = (
  SELECT
    job_id 
  FROM
    employees 
  GROUP BY
    job_id 
  ORDER BY
  avg( salary ) DESC 
  LIMIT 1)

案例5：查询平均工资高于公司平均工资的部门有哪些？

首先查询出公司的平均工资，然后再查询出各部门的平均工资，然后使用having进行筛选

SELECT
  department_id,
  avg( salary ) ag 
FROM
  employees 
GROUP BY
  department_id 
HAVING
  ag >(
  SELECT
    avg( salary ) ag 
  FROM
  employees)

案例6：查询出公司中所有manager的详细信息

首先查询出公司中所有的manager的id，然后使用in去做遍历

SELECT
  * 
FROM
  employees 
WHERE
  employee_id IN (
  SELECT DISTINCT
    manager_id 
  FROM
    employees 
WHERE
  manager_id IS NOT NULL)

案例7：各个部门中最高工资中最低的那个部门的最低工资是多少

SELECT
  min( salary ) 
FROM
  employees 
WHERE
  department_id = ( SELECT department_id FROM employees GROUP BY department_id ORDER BY max( salary ) LIMIT 1 );

案例8：查询平均工资最高的部门的manager的详细信息：last_name，department_id，email，salary

SELECT
  last_name,
  department_id,
  email,
  salary 
FROM
  employees 
WHERE
  employee_id IN ( SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL ) 
  AND department_id = ( SELECT department_id FROM employees GROUP BY department_id ORDER BY avg( salary ) DESC LIMIT 1 );

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

标签: mysql