SQL高级子查询、开窗函数、分页、排序、分区

高级子查询

1）如果一个SQL语句中出现多个SELECT,表示就是一个子查询

2）一个SQL语句的查询结果，是另一个SQL语句的查询条件

3）普通子查询的执行顺序，先执行内层子查询，再执行外层的主查询内容

4）子查询分为了 单行子查询，和多行子查询

- 单行子查询：内层子查询结果是一行数据

- 多行子查询：内层子查询结果是多行数据

5）多行子查询只能是多行运算符

- in :在指定列表里面

- any :满足任意一个数据

- all :满足所有的数据

*/
SELECT * 
FROM employees 
WHERE salary >(
      SELECT salary FROM employees WHERE last_name = 'Chen'
);

多列子查询

/*

单行子查询：单行记录

多行子查询：多行记录

组合：单行单列子查询

单行多列子查询

多行单列子查询

多行多列子查询

结论：1）如果是多行子查询，必须使用多行运算符进行运算

2）如果碰见的是多列子查询，使用条件的时候 必须使用括号()将字括起来

*/

-- 单列单行子查询

SELECT * 
FROM employees 
WHERE salary >(
      SELECT salary FROM employees WHERE last_name = 'Chen'
);

-- 多列单行子查询

-- 查询和“Chen”同一个公司，并且同一个部门，同一个岗位 员工信息
SELECT * 
FROM employees 
WHERE (department_id,job_id) = (
      SELECT department_id,job_id 
      FROM employees WHERE last_name = 'Chen'
);

-- 查询与141号或174号员工的manager_id和department_id相同的其他员工的
-- employee_id, manager_id, department_id
-- 多行 多列的查询   称为 成对比较  
SELECT  employee_id,last_name, manager_id, department_id
FROM employees
WHERE (manager_id,department_id) IN (
  SELECT manager_id,department_id
  FROM employees 
  WHERE employee_id IN(141,174)
);

-- 不成对比较：此时有BUG注意，否则查询出来的数据是有异常的
SELECT  employee_id,last_name, manager_id, department_id
FROM employees
WHERE manager_id IN (
  SELECT manager_id
  FROM employees 
  WHERE employee_id IN(141,174)
) AND department_id IN(
  SELECT department_id
  FROM employees 
  WHERE employee_id IN(141,174)
);

-- 制造一个特殊的记录：124原本是等于50  改一个数据50->80
UPDATE employees SET department_id = 80 WHERE employee_id = 142;
SELECT * FROM employees;

-- 在 FROM 子句中使用子查询
-- 将查询出来结果，当成另外一个表
-- 问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
SELECT last_name, e.department_id, salary,d.avg_salary FROM employees e,(
  SELECT department_id,AVG(salary) avg_salary
  FROM employees
  GROUP BY department_id) d 
WHERE e.department_id = d.department_id 
AND e.salary>d.avg_salary;

-- 在 CASE 表达式中使用单列子查询
-- 问题：显示员工的employee_id,last_name和location。
-- 其中，若员工department_id与location_id为1800的department_id相同，
-- 则location为’Canada’,其余则为’USA’。
SELECT employee_id,
       last_name,
       department_id,
       CASE  WHEN  department_id = (
                   SELECT department_id 
                   FROM departments 
                   WHERE location_id = 1800
             ) THEN 'Canada' ELSE 'USA' END "location"
FROM employees;
SELECT department_id 
FROM departments 
WHERE location_id = 1800

-相关子查询

1、简介

1）相关子查询的执行顺序，和普通子查询执行顺序是不同的

- 普通子查询：先执行内层的子查询，再执行外层的主查询

- 相关子查询：先执行外层的主查询，再执行内层的子查询

2）相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询

3）相关子查询必须满足一个条件：在内层的子查询中，使用了主查询相关的列

4）此时，内层的子查询不能单独运行。必须要和外层主查询一起执行

-- 问题：查询员工中工资大于本部门平均工资的员工的last_name,
--       salary和其department_id
-- 方案1：使用了多表查询完成（弊端：要的结果只来自于一张表，多表效率偏慢）
SELECT last_name, e.department_id, salary FROM employees e,(
  SELECT department_id,AVG(salary) avg_salary
  FROM employees
  GROUP BY department_id) d 
WHERE e.department_id = d.department_id 
AND e.salary>d.avg_salary;
-- 方案2：使用相关子查询来完成
-- e表：是在外层，也就是主查询
-- e2表：是在内存，也就是子查询
/*

相关子查询的执行过程（顺序）：

1）先对主