mysql多行子查询实战案例(只包含不相关子查询)_Mysql

多行子查询

集合比较子查询

内查询返回多行数据

我们这个时候使用=这个符号就不会合适了,多行子查询用到的符号应该是

多行比较操作符

any是和其中任一个值比较,all是和所有值比较

比如现在有女生是家务做的很好,美丽的,贤惠的,暴力了,any就是选其中一个就行,all就是全部都要

some实际是any的别名,和any的作用一致

多行子查询的操作符:in,any,all,some(any)

操作符:in

我们这里用等于是不行的,所以改为in

这里的需求就是查询首字母大于a的姓名和工作.当然我们前面的等号都可以替换为in,因为后面是括号嘛

操作符:any

题目1返回job_id中比job_id为it_prog部门任一工资低的员工的员工号,姓名,job_id,salary

where job_id in (select job_id from employees where last_name>'a')
select employee_id,last_name,job_id,salary
from employees
where salary<any(select salary
                  from employees
                  where job_id='it_prog')
and job_id<>'it_prog'

结果里有8000也有4000,因为只要比其中一个人低就可以了,也就是比最大值低就行

这是any函数的用法,我们再看看all的情况

操作符:all

题目2””返回job_id中比job_id为it_prog部门所有工资低的员工的员工号,姓名,job_id,salary

这里我们就用到all函数

where job_id in (select job_id from employees where last_name>'a')
select employee_id,last_name,job_id,salary
from employees
where salary<all(select salary
                  from employees
                  where job_id='it_prog')
and job_id<>'it_prog'

这里的值就少了很多,因为用的是all函数,所以结果必须比全部部门是’it_prog’部门工资低,所以最后的结果是小于最小值才行,条件上all是比较苛刻的,

大家体会一下这两个函数的区别

一个练习

题目:查询平均工资最低的部门id

这个看起来简单,实际有点难度,我们先把条件先把这个条件都分成很多步,我们先一步一步做,然后最后豁然开朗

我们先查各个部门的平均工资

select avg(salary)
                  from employees
                  group by department_id

我们比每一个都小是不是可以直接用min,这是不可以的,因为聚合函数是不能嵌套的,mysql是不支持聚合函数嵌套的,不过oracle是支持的,所以之后版本可能会支持.但是到9.0为止是还不支持的.

我们想想能不能用any和all解决,我们这个部门的工资现在是不是要比每一个部门的工资都要小,但是和最后一个是相等的,那么我们就可以用<=里面的每一个值.:这是方法1:

select department_id
from employees
where salary<=all(select avg(salary)
                  from employees
                  group by department_id )

这样写是错误的,因为我们要求的是平均工资小于,不能用where,要用group by和having组合起来使用,这个写法才是正确的,这个方法比较的简便.

select department_id
from employees
group by department_id
having avg(salary)<=all(select avg(salary)
                  from employees
                  group by department_id )

还有方法2,我们看(select avg(salary)

from employees

group by department_id这个是不是代表了一个表,那么我们希望从这个表里提取出这行的最小值,自然而然的我们直接from这张表,并用min函数,我们试一试

select avg_salary          
  from(select avg(salary)
                  from employees
                  group by department_id )

结果报错了,他所,我们需要给这个表一个别名,顺便给字段也来一个别名不然容易()出错

查出来之后我们就可以用min嵌套

所以这里我们也用了一个子查询,还是比较有意思的,接下来我们要查那个部门的工资等于这个值

这里我们就用到了三层的子查询.前面的方法比较简便一些.这个方法比较容易理解.

空值问题

我们想查询是管理者的员工.也就是员工id等于部门id的员工有那几个

select last_name
from employees
where employee_id in(select manager_id
                      from employees)

现在一共查出来18条,那么我们还有一个疑惑,可不可以查出来剩下不是管理者的员工呢,我们把in改成not in

select last_name
from employees
where employee_id not in(select manager_id
                      from employees)

发现结果是空表,不是我们期望的结果,这里不能简单的加个not,这是为什么呢,因为我们的结果里包含了null

select last_name
from employees
where employee_id not in(select manager_id
                      from employees
                      where manager_id is not null)