子查询出现的位置 88/*
select 后面 标量子查询
from 后面 表子查询
where或者having后面 标量子查询 列子查询（这两个重点） 行子查询
exists后面（相关子查询） 表子查询
子查询优于主查询

按结果集的行列数不同
标量子查询 11
列子查询 n1
行子查询 n*m 一行多列
表子查询 一般多行多列

*/

一 where或者having后面 标量子查询 列子查询
/*
子查询放在小括号里面
子查询一般在条件右侧
标量搭配 > < >= <= = <>
列子搭配 IN ANY/SOME ALL

/
#1 标量子查询 （单行子查询）
#1.1谁的工资比Abel高
SELECT salary #先查Abel的工资 再作为查员工的条件
FROM employees
WHERE last_name = ‘Abel’;

SELECT
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE last_name = ‘Abel’
);

#1.2 用了分组函数
#返回公司工资最少的员工的last_name,job_id,salary;
SELECT MIN(salary)
FROM employees;

SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);

1.3 用having后面
#查询最低工资大于50号部门最低工资的部门id和其最低工资
#1.3.1 查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50;

#1.3.2查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;

#1.3.3筛选<br />    SELECT MIN(salary),department_id<br />    FROM employees<br />    GROUP BY department_id<br />    HAVING MIN(salary)>(<br />            SELECT MIN(salary)<br />            FROM employees<br />            WHERE department_id = 50<br />    );

#2 ；列子查询 一列多行
# IN NOT IN ANY SOME ALL

#返回其他工种中比job_id为“IT_PROG”部门任一 工资低的员工名字
#2.1 查a 比a低
SELECT DISTINCT salary #distinct去重
FROM employees
WHERE job_id = ‘IT_PROG’;
#2.2 查名字 wheresalary SELECT last_name
from employees
WHERE salary< ANY (
SELECT DISTINCT salary
FROM employees
WHERE job_id = ‘IT_PROG’
);


#3行子查询 多行多列 一行多列 用的少
#查询员工编号最小并且工资最高的 员工信息

SELECT *
FROM employees
WHERE (id , salary)=(
select MIN(id),MAX(salary)
FROM employees
);

#SELECT 后面的子查询