postgresql-子查询

news/2024/7/9 21:25:27 标签: postgresql, 数据库

postgresql-子查询

  • 简介
  • 派生表
  • IN 操作符
  • ALL 操作符
  • ANY 操作符
  • 关联子查询
  • 横向子查询
  • EXISTS 操作符

简介

子查询(Subquery)是指嵌套在其他 SELECT、INSERT、UPDATE 以及 DELETE 语句中的
查询语句。
子查询的作用与多表连接查询有点类似,也是为了从多个关联的表中返回或者过滤数据。

例如,我们想要知道哪些员工的月薪大于平均月薪,可以通过子查询实现:

select e.first_name, e.last_name, e.salary
 from employees e
where salary > (select avg(salary) from employees);

在这里插入图片描述
其中,WHERE 子句中使用了一个子查询,用于计算平均月薪。PostgreSQL 在执行以上语句
时,先执行子查询返回平均月薪;然后将该值传递给外查询使用。
子查询必须位于括号中,也称为内查询,包含子查询的查询语句被称为外查询。除了 WHERE
子句之外,其他子句中也可以使用子查询,例如 SELECT 列表、FROM 子句等。

派生表

FROM 子句中的子查询被称为派生表(Derived table),语法如下:

SELECT column1, column2, ...
 FROM (subquery) AS table_alias;

其中子查询相当于创建了一个临时表 table_alias。以下语句用于获取每个部门的总月薪:

select d.department_name,
 ds.sum_salary
 from departments d
 join (select department_id,
 sum(salary) as sum_salary
 from employees
 group by department_id) ds
 on (d.department_id = ds.department_id);

在这里插入图片描述
其中,子查询返回了部门编号和部门月薪合计;然后再和 departments 表进行连接查询。

IN 操作符

如果 WHERE 子查询返回多个记录,可以使用 IN 操作符进行条件过滤:

SELECT d.department_id,
 d.department_name
 FROM departments d
WHERE d.department_id in (SELECT department_id FROM employees WHERE
hire_date >= date '2008-01-01');

以上查询返回了存在 2008 年 01 月 01 日以后入职员工的部门。如果想要返回包含该日期之
前入职的员工的部门,可以使用 NOT IN 操作符。
除了 IN 之外,还有一些其他进行类似过滤的操作符。

ALL 操作符

ALL 操作符与比较运算符一起使用,可以将一个值与子查询返回的列表进行比较:

SELECT first_name, last_name, salary
 FROM employees
WHERE salary > all (SELECT salary FROM employees WHERE department_id = 80);

以上语句返回了月薪比销售部门(department_id = 80)所有员工都高的员工。
其他比较运算符也可以与 ALL 进行组合,例如 salary < ALL 表示月薪比销售部门所有员工
都低的员工。

ANY 操作符

ANY 操作符和 ALL 操作符使用方法类似,只是效果不同:

SELECT first_name, last_name, salary
 FROM employees
WHERE salary > any (SELECT salary FROM employees WHERE department_id = 80);

在这里插入图片描述
以上语句返回了月薪比销售部门(department_id = 80)任何员工高的员工。
ANY 也可以和其他比较运算符一起使用,例如= ANY 实际上和 IN 的作用相同。
另外,SOME 和 ANY 是同义词。

关联子查询

有一类子查询,它们会引用外部查询中的列,因而与外部查询产生关联,被称为关联子查询。

返回月薪大于所在部门平均月薪的员工:

select first_name, last_name, salary
from employees o
where o.salary > (select avg(salary) from employees i where i.department_id
= o.department_id);

在这里插入图片描述
可以看到,子查询中使用了外查询的字段(o.department_id)。对于外部查询中的每个
员工,运行子查询返回他/她所在部门的平均月薪,然后传递给外部查询进行判断。

关联子查询对于外查询中的每一行都会运行一次(数据库可能会对此进行优化),而非
关联子查询在整个查询运行时只会执行一次

以下语句在 SELECT 列表中使用关联子查询,返回每个部门的总月薪,和上文示例
相同:

SELECT d.department_name,
 (SELECT SUM(salary)
 FROM employees e
 WHERE e.department_id = d.department_id) AS sum_salary
 FROM departments d
 ORDER BY d.department_name;

横向子查询

子查询只能引用外查询中的字段,而不能使用同一层级中其他表中的字段

SELECT d.department_name,
 t.avg_salary
 FROM departments d
 JOIN (SELECT avg(e.salary) AS avg_salary
 FROM employees e
 WHERE e.department_id = d.department_id) t;
SQL Error [42601]: ERROR: syntax error at end of input
 Position: 209

以上语句在 JOIN 中引用了左侧 departments 表中的字段,产生了语法错误。为此,我们需
要使用横向子查询(LATERAL subquery)。通过增加 LATERAL 关键字,子查询可以引用左侧
表中的列:

select d.department_name,
 t.sum_salary
 from departments d
cross join lateral (select sum(e.salary) as sum_salary
 from employees e
 where e.department_id = d.department_id) t;

以上语句同样返回了每个部门的名称和总月薪。

EXISTS 操作符

EXISTS 操作符用于检查子查询结果的存在性。如果子查询返回任何结果,EXISTS 返回 True;
否则,返回 False。

返回了存在 2008 年 01 月 01 日以后入职员工的部门

select 
d.department_id ,
d.department_name 
from cps.public.departments d 
where exists (
select 1 from cps.public.employees e where e.hire_date >= date('2008-01-01')
);

在这里插入图片描述
NOT EXISTS 操作符执行相反的操作,即子查询不返回任何结果,NOT EXISTS 返回 True;
否则,返回 False。

[NOT] IN 用于检查某个值是否属于(=)子查询的结果列表,[NOT] EXISTS 只检查子查询
结果的存在性。如果子查询的结果中存在 NULL,NOT EXISTS 结果为 True;但是,NOT IN 结
果为 False,因为 NOT (X = NULL) 的结果为 NULL。例如:

在这里插入图片描述
以上语句查找没有任何员工的部门,结果返回了 16 条记录。如果使用 NOT IN 操作符:

select d.department_id,
 d.department_name
 from departments d
where d.department_id not in (select department_id from employees);

在这里插入图片描述
查询没有返回任何结果,因为有一个员工不属于任何部门,导致子查询的结果中包含 NULL:


http://www.niftyadmin.cn/n/4985677.html

相关文章

云计算环境中高性能计算的挑战与对策

文章目录 云计算中的高性能计算挑战1. 资源竞争&#xff1a;2. 网络延迟&#xff1a;3. 数据传输效率&#xff1a;4. 虚拟化开销&#xff1a;5. 节点异构性&#xff1a; 高性能计算在云计算环境中的对策1. 定制化虚拟机镜像&#xff1a;2. 弹性资源调整&#xff1a;3. 高效数据…

《异常检测——从经典算法到深度学习》22 Kontrast: 通过自监督对比学习识别软件变更中的错误

《异常检测——从经典算法到深度学习》 0 概论1 基于隔离森林的异常检测算法 2 基于LOF的异常检测算法3 基于One-Class SVM的异常检测算法4 基于高斯概率密度异常检测算法5 Opprentice——异常检测经典算法最终篇6 基于重构概率的 VAE 异常检测7 基于条件VAE异常检测8 Donut: …

【微服务部署】07-调用链追踪

文章目录 集成SkyWalking实现调用链追踪1. SkyWalking架构图2. 代码集成SkyWalking 集成SkyWalking实现调用链追踪 1. SkyWalking架构图 Receiver是SkyWalking的入口&#xff0c;支持gRPC和HTTP协议。 SkyWalking内部有分析和查询两个部分 存储方面SkyWalking支持Elasticsearc…

windows 中pycharm中venv无法激活

1.用管理员身份打开Windows PowerShell 2.进入项目的&#xff1a;venv\Scripts 如&#xff1a;D: (1): cd .\project\venv\Scripts\ (2): 执行命令&#xff1a; Set-ExecutionPolicy RemoteSigned (3): 选择&#xff1a;Y (4): .\activate

Jupyter installation Tutorial

文章目录 1. 面向的系统2. 什么是Jupyter&#xff1f;3. 安装Python环境4. 安装Jupyter notebook5. Jupyter的启动和配置6. Jupyter的使用技巧7. conclusion参考文献 1. 面向的系统 Windows安装 2. 什么是Jupyter&#xff1f; Jupyter Notebook是一个开源的Web应用程序&…

C# Timer定时器

C# Timer定时器 Timer定时器定时器主要用到的就是Timer的Tick事件&#xff0c;另外还要设置时间间隔&#xff1a; 下面这个实力演示了每隔一秒&#xff0c;picturebox中的图片来回切换&#xff0c;每隔一秒&#xff0c;文本框中显示当前时间。 using System; using System.Co…

关于MySQL数据库版本不同导致表进行比较的时候报错illegal mix of collations...的问题

问题发生的原委 之前在项目开发的时候&#xff0c;我本地也建立了数据库用作开发库&#xff0c;我本地的数据库版本是5.7的&#xff0c;但是测试和生产库都是8.0的版本&#xff0c;我们定义的数据库字符集是utf8mb4&#xff0c;排序规则是utf8mb4_general_ci&#xff0c;前段时…

​无需测试环境!如何利用测试脚手架隔离微服务,实现功能自动化

想在不建立完整测试环境的情况下测试微服务&#xff1f; 想在将变更推送到主线分支之前完成测试&#xff1f; 这是我们在进行项目交付时经常遇到的难题。最近&#xff0c;当我们开始一个新的项目&#xff0c;为客户构建一个新的聚合平台时&#xff0c;我们希望将尽可能多的测…