PostgreSQL | CTE | 使用with子句的通用表达式

2023-10-17 00:44

本文主要是介绍PostgreSQL | CTE | 使用with子句的通用表达式,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

CTE(Common Table Expressions)

简单讲,CTE就是日常SQL中出现的with语句,其原理就是通过提前将数据查询出来后作为临时结果集使用,可以与SELECT \ INSERT \ UPDATE \ DELETE的SQL连用。

优点

  1. 可读性强
    • CTE 允许你将复杂的查询拆分成易于理解和管理的块。这使得查询更易于阅读、理解和维护。
  2. 重用性
    • CTE 可以在一个查询中多次引用,这使得可以将复杂的逻辑组件分解成可重复使用的部分。
  3. 递归查询
    • CTE 允许你执行递归查询,这是一种对于层次化数据结构(如组织结构或树形结构)非常有用的功能。
  4. 优化器支持
    • PostgreSQL 的查询优化器可以对 CTE 进行优化,以确保最佳执行计划。

缺点

  1. 性能开销
    • 在某些情况下,使用 CTE 可能会导致性能开销。在处理大量数据时,可能会出现性能下降。
  2. 可读性降低
    • 尽管 CTE 可以提高可读性,但如果不正确使用,可能会导致查询变得更难理解。特别是在多个 CTE 之间建立复杂的关系时。
  3. 内存消耗
    • CTE 通常需要在内存中存储临时结果集,因此对于大型数据集可能会导致内存消耗较高。
  4. 不能在索引中使用
    • 不能在 CTE 中创建索引,这可能会导致在某些情况下查询性能下降。
  5. 递归查询潜在的性能问题
    • 对于大型或者深度很深的递归查询,可能会导致性能问题。

示例

  1. INSERT - 插入

    WITH r AS (SELECT code, nameFROM t1
    )
    INSERT INTO t2(code,name)
    SELECT code,name
    FROM r;
    
  2. UPDATE - 更新

    WITH r AS (SELECT code, nameFROM t1
    )
    UPDATE t2 
    SET t2.name = t1.name
    FROM t1
    WHERE t1.code = t2.code;
    
  3. SELECT - 查询

    WITH r AS (SELECT code, nameFROM t1
    )
    SELECT t2.*
    FROM t2
    WHERE EXISTS (SELECT 1 FROM t1 where t1.code = t2.code);
    
  4. DELETE - 删除

    WITH r AS (SELECT code, nameFROM t1
    )
    DELETE FROM t2
    WHERE code IN (SELECT code FROM t1);
    
  5. RECURSIVE - 递归查询

    WITH RECURSIVE r AS (SELECT id, name, parent_id, 1 as levelFROM organizationWHERE parent_id IS NULLUNION ALLSELECT o.id, o.name, o.parent_id, oh.level + 1FROM organization oJOIN r oh ON o.parent_id = oh.id
    )
    SELECT id, name, level
    FROM r;

WITH在一定程度能 解决数据库查询上的一些问题,但并不是每次适合,需要对照上述的优缺点,自行判断是否需要使用。



🎉如果对你有所帮助,可以点赞、关注、收藏起来,不然下次就找不到了🎉


【点赞】⭐️⭐️⭐️⭐️⭐️
【关注】⭐️⭐️⭐️⭐️⭐️
【收藏】⭐️⭐️⭐️⭐️⭐️

Thanks for watching.
Kenny

这篇关于PostgreSQL | CTE | 使用with子句的通用表达式的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/221806

相关文章

使用Python实现矢量路径的压缩、解压与可视化

《使用Python实现矢量路径的压缩、解压与可视化》在图形设计和Web开发中,矢量路径数据的高效存储与传输至关重要,本文将通过一个Python示例,展示如何将复杂的矢量路径命令序列压缩为JSON格式,... 目录引言核心功能概述1. 路径命令解析2. 路径数据压缩3. 路径数据解压4. 可视化代码实现详解1

Pandas透视表(Pivot Table)的具体使用

《Pandas透视表(PivotTable)的具体使用》透视表用于在数据分析和处理过程中进行数据重塑和汇总,本文就来介绍一下Pandas透视表(PivotTable)的具体使用,感兴趣的可以了解一下... 目录前言什么是透视表?使用步骤1. 引入必要的库2. 读取数据3. 创建透视表4. 查看透视表总结前言

Python 交互式可视化的利器Bokeh的使用

《Python交互式可视化的利器Bokeh的使用》Bokeh是一个专注于Web端交互式数据可视化的Python库,本文主要介绍了Python交互式可视化的利器Bokeh的使用,具有一定的参考价值,感... 目录1. Bokeh 简介1.1 为什么选择 Bokeh1.2 安装与环境配置2. Bokeh 基础2

Android使用ImageView.ScaleType实现图片的缩放与裁剪功能

《Android使用ImageView.ScaleType实现图片的缩放与裁剪功能》ImageView是最常用的控件之一,它用于展示各种类型的图片,为了能够根据需求调整图片的显示效果,Android提... 目录什么是 ImageView.ScaleType?FIT_XYFIT_STARTFIT_CENTE

Java学习手册之Filter和Listener使用方法

《Java学习手册之Filter和Listener使用方法》:本文主要介绍Java学习手册之Filter和Listener使用方法的相关资料,Filter是一种拦截器,可以在请求到达Servl... 目录一、Filter(过滤器)1. Filter 的工作原理2. Filter 的配置与使用二、Listen

Pandas使用AdaBoost进行分类的实现

《Pandas使用AdaBoost进行分类的实现》Pandas和AdaBoost分类算法,可以高效地进行数据预处理和分类任务,本文主要介绍了Pandas使用AdaBoost进行分类的实现,具有一定的参... 目录什么是 AdaBoost?使用 AdaBoost 的步骤安装必要的库步骤一:数据准备步骤二:模型

使用Pandas进行均值填充的实现

《使用Pandas进行均值填充的实现》缺失数据(NaN值)是一个常见的问题,我们可以通过多种方法来处理缺失数据,其中一种常用的方法是均值填充,本文主要介绍了使用Pandas进行均值填充的实现,感兴趣的... 目录什么是均值填充?为什么选择均值填充?均值填充的步骤实际代码示例总结在数据分析和处理过程中,缺失数

如何使用 Python 读取 Excel 数据

《如何使用Python读取Excel数据》:本文主要介绍使用Python读取Excel数据的详细教程,通过pandas和openpyxl,你可以轻松读取Excel文件,并进行各种数据处理操... 目录使用 python 读取 Excel 数据的详细教程1. 安装必要的依赖2. 读取 Excel 文件3. 读

解决Maven项目idea找不到本地仓库jar包问题以及使用mvn install:install-file

《解决Maven项目idea找不到本地仓库jar包问题以及使用mvninstall:install-file》:本文主要介绍解决Maven项目idea找不到本地仓库jar包问题以及使用mvnin... 目录Maven项目idea找不到本地仓库jar包以及使用mvn install:install-file基

最详细安装 PostgreSQL方法及常见问题解决

《最详细安装PostgreSQL方法及常见问题解决》:本文主要介绍最详细安装PostgreSQL方法及常见问题解决,介绍了在Windows系统上安装PostgreSQL及Linux系统上安装Po... 目录一、在 Windows 系统上安装 PostgreSQL1. 下载 PostgreSQL 安装包2.