mysql 子查询

子查询

概念：

嵌套在另一个查询中的查询语句称为子查询，外部的查询称为主查询，类似于java中的内部类

语法

select字段1，字段2

from表1

where  Id  in(

select字段1    from  表2

)

特点或者说注意事项：

1、子查询放在小括号内

2、放在条件右侧

3、子查询优先于主查询执行

分类：

单行子查询：子查询的结果只有一个值，使用单行操作符（> < >= <= = <>）

多行子查询:使用多行操作符（any、all、in、not in）

子查询案例

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

select min(salary) from employees

SELECT department_id FROM employees

where salary = (

  select min(salary) from employees

)

SELECT last_name,salary FROM employees

WHERE department_id = (

  SELECT department_id FROM employees

where salary = (

  select min(salary) from employees

)

)



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

SELECT avg(salary) from employees

WHERE department_id is not null

group by department_id

SELECT min(msalary) from(

SELECT avg(salary) msalary from employees

WHERE department_id is not null

group by department_id

)a

SELECT department_id

FROM employees

group by department_id

HAVING avg(salary) = (

  SELECT min(msalary) from(

    SELECT avg(salary) msalary from employees

    WHERE department_id is not null

    group by department_id

)a

)

SELECT * FROM departments

WHERE department_id = (

  SELECT department_id

FROM employees

group by department_id

HAVING avg(salary) = (

  SELECT min(msalary) from(

    SELECT avg(salary) msalary from employees

    WHERE department_id is not null

    group by department_id

)a

)

)

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

SELECT * FROM departments d

WHERE department_id = (

  SELECT department_id

FROM employees

group by department_id

HAVING avg(salary) = (

  SELECT min(msalary) from(

    SELECT avg(salary) msalary from employees

    WHERE department_id is not null

    group by department_id

)a

)

)

# 4. 查询平均工资最高的 job 信息

SELECT job_id, avg(salary) FROM employees

GROUP BY job_id

SELECT job_id, max(msalary) FROM (

  SELECT job_id, avg(salary) as msalary

FROM employees

GROUP BY job_id

)a

SELECT job_id o FROM employees e

INNER JOIN  ( SELECT job_id j, max(msalary) FROM (

  SELECT job_id, avg(salary) as msalary

FROM employees

GROUP BY job_id

)a

) b on e.job_id = b.j

SELECT * FROM jobs

where job_id = (

  SELECT job_id o FROM employees e

INNER JOIN  ( SELECT job_id j, max(msalary) FROM (

  SELECT job_id, avg(salary) as msalary

FROM employees

GROUP BY job_id

)a

) b on e.job_id = b.j

)

# 5. 查询平均工资高于公司平均工资的部门有哪些?

SELECT avg(salary) a FROM employees

SELECT department_id, avg(salary) b FROM employees

where department_id is not null

GROUP BY department_id

having b > (

  SELECT avg(salary) a FROM employees

)

SELECT DISTINCT department_id FROM employees e

inner join (

  SELECT department_id dep, avg(salary) b FROM employees

  where department_id is not null

  GROUP BY department_id

  having b > (

  SELECT avg(salary) a FROM employees

)

) d

on e.department_id = d.dep

# 6. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

SELECT department_id,avg(salary) FROM employees

WHERE department_id is not null

GROUP BY department_id

SELECT max(msalary) FROM (

  SELECT department_id,avg(salary) msalary FROM employees

WHERE department_id is not null

GROUP BY department_id

)a

SELECT department_id FROM (

  SELECT department_id,avg(salary) msalary FROM employees

WHERE department_id is not null

GROUP BY department_id

) b

WHERE msalary = (

SELECT max(msalary) FROM (

  SELECT department_id,avg(salary) msalary FROM employees

WHERE department_id is not null

GROUP BY department_id

) a

)

SELECT last_name, department_id, email, salary from employees

WHERE employee_id = (

SELECT manager_id from departments WHERE department_id =(

SELECT department_id FROM (

  SELECT department_id,avg(salary) msalary FROM employees

WHERE department_id is not null

GROUP BY department_id

) b

WHERE msalary = (

SELECT max(msalary) FROM (

  SELECT department_id,avg(salary) msalary FROM employees

WHERE department_id is not null

GROUP BY department_id

) a

)

)

)

# 部门的有效信息

select last_name,department_id,email,salary,manager_id from employees where manager_id = (

  select manager_id from departments where department_id = (

    select department_id from employees

    where department_id is not NULL

    group by department_id

    having avg(salary) = (

        select max(dsalary) from (

            select avg(salary) dsalary,department_id from employees

            where department_id is not NULL

            group by department_id

            having dsalary > (

              select avg(salary) from employees

            )

        ) a

    )

  )

)