高级子查询
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）先对主查询进行预查询
2）子查询中使用了主查询中的条件
3）主查询的每一行记录都会执行一次子查询
4）返回满足条件的列
注意：相关子查询中子查询不能独立运行！
*/
SELECT employee_id, last_name, e.department_id, salary
FROM employees e
WHERE e.salary>(
SELECT AVG(salary) avg_salary
FROM employees e2
WHERE e.department_id = e2.department_id
-- GROUP BY department_id -- 要不要分组效果是一样的
);-- 问题：查询员工的employee_id,last_name,要求按照员工的department_name排序
-- 方案1：多表实现
SELECT e.employee_id,e.last_name,e.department_id,d.department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id
ORDER BY d.department_name ASC,e.employee_id DESC;
-- 方案2：子查询
SELECT e.employee_id,e.last_name,e.department_id
FROM employees e
ORDER BY (
SELECT d.department_name
FROM departments d
WHERE d.department_id = e.department_id
),e.employee_id DESC;-- 问题：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，
-- 输出这些相同id的员工的employee_id,last_name和其job_id
-- 解释:查询出员工表中 调整岗位大于等于2的员工信息
SELECT * FROM employees;
-- 表示换岗位历史记录表
SELECT * FROM job_history;
SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2<=(
SELECT count(1)
FROM job_history j
WHERE e.employee_id = j.employee_id
);-升级版的相关子查询：EXISTS 和 NOT EXISTS
/*
1）查询效率比普通的相关子查询效率更高了
2）EXISTS只会返回TRUE或者FALSE
如果判断条件可以在子查询全部搞定搞定，那么推荐使用 EXISTS
*/
-- 修改以上的语句
SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2<=(
SELECT count(1)
FROM job_history j
WHERE e.employee_id = j.employee_id
);
-- 此时：可以在子查询过程中得到个数大于2的员工信息
SELECT employee_id,last_name,job_id
FROM employees e
WHERE EXISTS (
SELECT 'x' -- 表示一个固定值，可以是任意的
FROM job_history j
WHERE e.employee_id = j.employee_id
HAVING COUNT(1)>=2
);-- 不存在：NOT EXISTS
SELECT employee_id,last_name,job_id
FROM employees e
WHERE NOT EXISTS (
SELECT 'x' -- 表示一个固定值，可以是任意的
FROM job_history j
WHERE e.employee_id = j.employee_id
HAVING COUNT(1)>=2
);--- 优化的路径:多表->相关子查询->EXISTS
-- 相关更新：使用相关子查询依据一个表中的数据更新另一个表的数据-------------------
-- 需求：为了提高查询效率，故意添加冗余字段，把多表变成单表
-- 将departments表中的department_name字段添加到employees员工表中
CREATE TABLE emp_567
AS SELECT * FROM employees WHERE 1=1;
SELECT * FROM emp_567;
ALTER TABLE emp_567 ADD dname VARCHAR2(32);
select * from departments;-- 需求：修改emp_567表中的dame,内容就是对应 departments表中的name
UPDATE emp_567 e
SET dname = (
SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id
);
SELECT * FROM emp_567;-- 相关删除：使用相关子查询依据一个表中的数据删除另一个表的数据------------------
-- 问题：删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees E
WHERE employee_id =
(SELECT employee_id
FROM emp_history
WHERE employee_id = E.employee_id);WITH:字句
WITH字句的作用：
在表进行多次查询的时候，减少对磁盘的查询速度，
可以通过WITH自己临时生成一张表，加载到内容中，提高查询效率
在一些复杂的，多次查询过程中，WITH很重要了
语法：
WITH
临时表1 AS (
子查询....
),
临时表2 AS (
子查询....
),
临时表3 AS (
子查询....
)
SELECT * FROM 临时表1,[临时表2,临时表3]-- 问题：查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
WITH
dept_sum_tbl AS (
SELECT d.department_id,d.department_name,e.dept_sum_sal
FROM departments d,(
SELECT department_id,SUM(salary) dept_sum_sal
FROM employees
GROUP by department_id
) e WHERE d.department_id = e.department_id
),
dept_avg_tbl AS(
SELECT avg(dept_sum_sal) dept_avg_sal
FROM dept_sum_tbl
)
SELECT *
FROM dept_sum_tbl t1
WHERE t1.dept_sum_sal>(
SELECT dept_avg_sal FROM dept_avg_tbl
);CREATE TABLE xxx AS select * FROM (
WITH
dept_sum_tbl AS (
SELECT d.department_id,d.department_name,e.dept_sum_sal
FROM departments d,(
SELECT department_id,SUM(salary) dept_sum_sal
FROM employees
GROUP by department_id
) e WHERE d.department_id = e.department_id
),
dept_avg_tbl AS(
SELECT avg(sum_tbl.dept_sum_sal) dept_avg_sal
FROM dept_sum_tbl sum_tbl
)
SELECT *
FROM dept_sum_tbl t1
WHERE t1.dept_sum_sal>(
SELECT dept_avg_sal FROM dept_avg_tbl
))
select * from xxx;-开窗函数|窗口函数|分析函数
/* https://www.cnblogs.com/lihaoyang/p/6756956.html
1、简介
1）与聚合函数一样，开窗函数也是对行集组进行聚合计算
2）聚合函数每组只返回一个值，但是开窗函数可以同时返回多个值
3）因为开窗函数所执行聚合计算的行集组是窗口。在 ISO SQL 规定了这样的函数为开窗函数，
在 Oracle 中则被称为分析函数。
4）MySql8.x版本以后才支持开窗函数，企业常用版本：mysql5.7
2、语法
函数名() OVER(选项)
注意：开窗函数不能独立运行，必须借助于具体的方法
常用的函数
MAX()
MIN()
AVG()
SUM()
COUNT()
LAG()
LEAD()
常用的选项
PARTITION BY: 分区运算
ORDER BY: 排序
3、使用场景
一般需要常规字段信息，也需要统计相关信息，推荐使用开窗函数
例如：常用员工的信息（姓名，薪资）和该员工的人数
*/
-- 需求：查询员工编号，员工姓名，岗位，工资，部门编号，部门的人数
-- 1）常规方式
-- 弊端：常规的聚合函数，只返回一个值，所以不能和普通列一起出现
SELECT e.employee_id,
e.last_name,
e.job_id,
e.salary,
e.department_id,
d.cnt
FROM employees e,(
SELECT department_id,
COUNT(1) cnt
FROM employees
GROUP BY department_id
)d WHERE e.department_id = d.department_id
-- 2）开窗函数
SELECT e.employee_id,
e.last_name,
e.job_id,
e.salary,
e.department_id,
COUNT(1) OVER(PARTITION BY e.department_id) CNT
FROM employees e;测试表 T_Person
CREATE TABLE T_Person (FName VARCHAR2(20),FCity VARCHAR2(20),FAge INT,FSalary INT);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Tom','BeiJing',20,3000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Tim','ChengDu',21,4000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Jim','BeiJing',22,3500);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Lily','London',21,2000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('John','NewYork',22,1000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('YaoMing','BeiJing',20,3000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Swing','London',22,2000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Guo','NewYork',20,2800);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('YuQian','BeiJing',24,8000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Ketty','London',25,8500);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Kitty','ChengDu',25,3000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Merry','BeiJing',23,3500);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Smith','ChengDu',30,3000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Bill','BeiJing',25,2000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Jerry','NewYork',24,3300);
COMMIT;
SELECT * FROM t_person;-- 需求：比如我们想查询每个工资小于 5000 元的员工信息（城市以及年龄），并
-- 且在每行中都显示所有工资小于 5000 元的员工个数：
SELECT fname,fage,fsalary,
--(select count(*) from t_person where fsalary < 5000) 工资少于5000员工总数
COUNT(1) OVER() CNT
FROM t_person
WHERE fsalary<5000;PARTITION BY 子句
/*
1、简介
1）功能类似GROUP BY 分组计算
2）如果不适用 PARTITION BY会对所有的结果集进行统计
3）如果使用PARTITION BY会按照指定的范围进行统计
4）与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独
立于结果集的，创建的分区只是供进行聚合计算的
5）而且不同的开窗函数所创建的分区也不互相影响
6）PARTITION BY也可以像GROUP BY 同时对多个字段进行分区
*/
SELECT COUNT(1) FROM employees;
SELECT COUNT(1) FROM employees GROUP BY department_id;-- 需求：显示每一个人员的信息以及所属城市的人员数，以及相同年龄的人数
SELECT fname,fcity,fage,
COUNT(1) OVER(PARTITION BY fcity) ccont,
COUNT(1) OVER(PARTITION BY fage) fcount,
FROM t_person;-- 查询每个城市，不同年龄段的人数
SELECT fname,fcity,fage,
COUNT(1) OVER(PARTITION BY fcity,fage) ccont
FROM t_person;ORDER BY子句
/*
1、简介
1）ORDER BY用来指定排序规则
2）而且有部分函数必须要指定排序规则。例如 RANK() ROW_NUMBER()...
2、语法
ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2
*/
-- 需求：例子程序一：查询从第一行到当前行的工资总和：
SELECT fname,fcity,fage,fsalary,
SUM(fsalary)
OVER(ORDER BY fsalary ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) sum1
FROM t_person;用法示例
SELECT fname,fcity,fage,fsalary,
SUM(fsalary)
OVER(ORDER BY fsalary ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) sum1
FROM t_person;-- ROWS :按照单行数据进行计算
-- RANGE :按照范围数据进行计算
SELECT fname,fcity,fage,fsalary,
SUM(fsalary)
-- OVER(ORDER BY fsalary ASC) sum1 --默认值 和下面的一模一样
OVER(ORDER BY fsalary ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum1
FROM t_person;-- ORDER BY 默认值：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW-- 同比，环比
-- 计算：从上一行 到当前行的总和
SELECT fname,fcity,fage,fsalary,
SUM(fsalary)
OVER(ORDER BY fsalary ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) sum1
FROM t_person;-- 计算：从上一行 到后两行 总共4行数据的和
SELECT fname,fcity,fage,fsalary,
SUM(fsalary)
OVER(ORDER BY fsalary ASC ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING ) sum1
FROM t_person;-- 计算：后面一到三之和
SELECT fname,fcity,fage,fsalary,
SUM(fsalary)
OVER(ORDER BY fsalary ASC ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING ) sum1
FROM t_person;分页查询
通过ROWNUM伪列来实现分页查询
也可以通过开窗函数函数实现排名
SELECT fname,fcity,fage,fsalary,
COUNT(1)
OVER(ORDER BY fsalary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) rn
FROM t_person;-- 开窗函数 直接提供了 三个类似的函数
-- ROW_NUMBER();
-- rank() ,
-- dense_rank()
-- 例子程序6：over(partition by XX order by XX) partition by和order by 结合-- 需求：员工信息+同龄人最高工资，按工资排序
SELECT fname,fcity,fage,fsalary,
COUNT(1)
OVER(PARTITION BY fage ORDER BY fsalary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) rn
FROM t_person;-- 需求：员工信息+城市最高工资，按工资排序
SELECT fname,fcity,fage,fsalary,
COUNT(1)
OVER(PARTITION BY fcity ORDER BY fsalary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) rn
FROM t_person;---------------开窗函数除了和聚合函数一起使用，提供了其他函数-----------
/*
排名相关的函数
ROW_NUMBER();
rank() ,
dense_rank()
-- lag() over(partition by ... order by):取出前n行的数据
SELECT t.*,LAG(fsalary,1) OVER(ORDER BY fsalary) FROM t_person t; -- lead() over(partition by ... order by):取出后n行的数据
SELECT t.*,LEAD(fsalary,1) OVER(ORDER BY fsalary) FROM t_person t; -- first_value() over(partition by ... order by):返回分区中的第一个值
SELECT t.*,FIRST_VALUE(fsalary) OVER(PARTITION BY fcity ORDER BY fsalary)
FROM t_person t; -- last_value() over(partition by ... order by):返回分区中的最后一个值
SELECT t.*,LAST_VALUE(fsalary) OVER(PARTITION BY fcity ) FROM t_person t; -- ntile 分区，分组
CREATE TABLE demo1234(sid varchar2(10),sname varchar2(10));*/
排序相关的函数
SELECT fname,fcity,fage,fsalary,
ROW_NUMBER() OVER(ORDER BY fsalary DESC) rn,
RANK() OVER(ORDER BY fsalary DESC) rk,
DENSE_RANK() OVER(ORDER BY fsalary DESC) drk
FROM t_person;-- ROW_NUMBER():总是从第1排到最后一个
-- RANK() 出现并排的情况，后面的内容就会空出来，最终排序大小不变的
-- DENSE_RANK() 出现并排的情况，后面的内容接下一个排名。最终排序数量可能变小
-- lag() over(partition by ... order by):取出前n行的数据
SELECT t.*,LAG(fsalary,1) OVER(ORDER BY fsalary) FROM t_person t;-- 前2行
SELECT t.*,LAG(fsalary,2) OVER(ORDER BY fsalary) FROM t_person t; -- lead() over(partition by ... order by):取出后n行的数据
SELECT t.*,LEAD(fsalary,1) OVER(ORDER BY fsalary) FROM t_person t;-- first_value() over(partition by ... order by):返回分区中的第一个值
SELECT t.*,FIRST_VALUE(fsalary) OVER(PARTITION BY fcity ORDER BY fsalary)
FROM t_person t;-- last_value() over(partition by ... order by):返回分区中的最后一个值
SELECT t.*,LAST_VALUE(fsalary) OVER(PARTITION BY fcity ) FROM t_person t;ntile 分区，分组
CREATE TABLE demo1234(sid varchar2(10),sname varchar2(10));
INSERT INTO demo1234 VALUES('1','A');
INSERT INTO demo1234 VALUES('2','B');
INSERT INTO demo1234 VALUES('/','C');
INSERT INTO demo1234 VALUES('4','D');
INSERT INTO demo1234 VALUES('5','E');
INSERT INTO demo1234 VALUES('6','F');
INSERT INTO demo1234 VALUES('7','G');
INSERT INTO demo1234 VALUES('8','H');
INSERT INTO demo1234 VALUES('/','I');
INSERT INTO demo1234 VALUES('10','J');
INSERT INTO demo1234 VALUES('11','L');
COMMIT;
SELECT t.*,ntile(2) OVER(ORDER BY sname) x FROM demo1234 t;-- 分配规则：
-- 1）尽可能的平均分配 2）如果分布均匀，尽可能满足前面的分区
总结
-- 1）OVER()表示开窗
-- 2）开窗选项配合：PARTITION BY 和 ORDER BY一起使用
-- 3）所有的聚合函数可以开窗 ()
MAX() MIN() AVG() SUM() COUNT()
-- 4) 其他常用的函数
ROW_NUMBER()
RANK()
DENSE_RANK()
LAG()
LEAD()
FIRST_VALUE()
LAST_VALUE()
NTILE()
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
