34.子查询简介-526互联

简介

定义：子查询指一个查询语句嵌套在另一个查询语句内部，在SELECT子句中先计算子查询，子查询的结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。这个特性从MySQL 4.1开始引入。
子查询作为过滤条件时需要用（） 包裹

子查询的常见分类

From型子查询：将子查询的结果作为父查询的表来使用
in/not in 型子查询：子查询的结果是单列多行，作为where的过滤条件
where型子查询：查询结果作为过滤条件出现在比较运算符的一端

带From关键词的子查询

子查询是一张多行多列的表，将子查询作为父查询的表来嵌套查询
子查询语句必须用（）包裹且需要有别名
计算出各部门性别为男性的员工人数


select dept_name,count(emp_id) from 
(select dept_name, emp_id,ename,gender from 
dept inner join emp_part
 where id=dept_id and gender='男')b group by dept_name;

带IN关键词的子查询

将子查询作为where语句后的过滤条件，常用于子查询结果是单列多行的情况
子查询语句必须用（）包裹
in/not in
查询出北京地区所有的员工信息

SELECT *
FROM emp_part
WHERE  dept_id IN (SELECT id FROM dept WHERE dept_location = '北京')

带比较运算符的子查询

将子查询的结果作为过滤条件，放在比较运算符的一端
常用于子查询结果为单个结果的情况
子查询语句必须用（）包裹


#查询出薪资大于公司平均薪资的员工id,姓名及薪资
SELECT emp_id,ename,salary FROM emp_part 
WHERE salary > (SELECT AVG(salary) FROM emp_part);

with as

如果一整句查询语句中，某个子查询的结果会被多个父查询引用，通常建议将共用的子查询用简写表示出来
语法： with [表名] as （select…)

# 查询出部门平均薪资大于公司平均薪资的部门名称，部门主管，所在地及部门平均薪资

# 不使用 with ...as
select dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join 
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b on id=dept_id 
and avg_salary > (select avg(avg_salary) from 
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b);

# 使用 with ...as
with dept_avg as
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)select
dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join dept_avg 
on id=dept_id 
and avg_salary > (select avg(avg_salary) from dept_avg);