CTE查询

​专栏内容：

• postgresql内核源码分析
• 手写数据库toadb
• 并发编程

  ​开源贡献：

  • toadb开源库

    个人主页：我的主页

    管理社区：开源数据库

    座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

系列文章

• 入门准备
• postgrersql基础架构
• 快速使用
• 初始化集群
• 数据库服务管理
• psql客户端使用
• pgAdmin图形化客户端
• 数据库的使用
• 创建数据库
• 数据库操作
• 表的使用
• 表的创建
• 表的操作
• 数据查询
• 数据查询
• 多表联合查询
• 数据操作
• 插入数据的方式

  文章目录

  • CTE查询
  • 系列文章
  • 前言
  • 概述
  • CTE语法介绍
  • 数据准备
  • 基本应用
  • 多个CTE
  • • 并行的 CTE
    • 递进的 CTE
    • 递归查询
    • • 简单递归查询
      • 总结
      • 结尾

        前言

        ---

        postgresql 数据库是一款通用的关系型数据，在开源数据库中能与商业数据媲美，在业界也越来越流行。

        因为是开源数据库，不仅公开源码，还有很多使用案例，好用的插件，所以它的慢慢变成了数据库的先驱和标准，通过postgresql可以很好从使用到原理，彻底搞懂；

        如果是学习编程，也可以学到丰富的编程知识，数据结构，编程技巧，它里面还有很多精妙的架构设计，分层思想，可以灵活定制的思想。

        本专栏主要介绍postgresql 入门使用，数据库维护管理，通过这些使用来了解数据库原理，慢慢了解postgresql是什么样的数据库，能做那些事情，以及如何做好服务，最关键的是这些知识都是面试的必备项。

        概述

        ---

        在一些分析型业务中，通常会遇到一些非常复杂的SQL语句，多个子查询的联合，此时可以使用 common table expression (CTE) 方式的SQL写法，来让SQL更加简化和易于理解，本文就来分享一下postgresql中的CTE语言的写法，以及使用案例。

        CTE语法介绍

        ---

        common table expression (CTE), 翻译过来就是通用表表达式，它将子查询创建成临时表的形式，通过with子句嵌套在查询语句中，在主查询中引用这些临时表就可以，简化了普通SQL的写法，更加易于理解和维护。

        CTE的SQL形式如下：

        WITH cte_name (column1, column2, ...) AS (
            -- 临时表查询
            SELECT ...
        )
        -- 主查询，使用CTE
        SELECT ...
        FROM cte_name;
        

        它主要包括以下几部分：

        • with子句，该关键字指示CTE语句的开始；
        • CTE表达式名, 指定CTE名字，名字有效范围是整个语句，所以整个语句中不能重名；
        • CTE目标列名，这是可选项，不指定时，默认为cte查询语句的select目标列;
        • CTE查询语句，它包括在AS关键字对应的括号中；可以是任意的SQL语句；
        • 主查询语句，主查询语句得到整个SQL的最终结果，它可以像引用普通表一样来引用CTE表达式名，这样就可以将复杂SQL拆解成几个CTE表达式，然后再引用；

          数据准备

          ---

          下面针对CTE的应用场景进行举例说明，为了方便举例，先创建以下几张表。

          1. 产品表 (Products)

          这个表用于存储产品的信息。

          字段	类型	描述
          product_id	INT PRIMARY KEY	产品的唯一标识符
          product_name	VARCHAR	产品的名称
          price	DECIMAL	产品的价格
          category	VARCHAR	产品的类别

          2. 订单表 (Orders)

          这个表用于存储订单的信息。

          字段	类型	描述
          order_id	INT PRIMARY KEY	订单的唯一标识符
          product_id	INT	与产品表关联的外键
          quantity	INT	订购的产品数量
          region	VARCHAR	订单所在的区域
          order_date	DATE	订单的日期

          -- 创建产品表  
          CREATE TABLE products (  
              product_id INT PRIMARY KEY,  
              product_name VARCHAR(255) NOT NULL,  
              price DECIMAL(10, 2) NOT NULL,  
              category VARCHAR(255)  
          );  
            
          -- 创建订单表  
          CREATE TABLE orders (  
              order_id INT PRIMARY KEY,  
              product_id INT,  
              quantity INT NOT NULL,  
              region VARCHAR(255) NOT NULL,  
              order_date DATE NOT NULL,  
              FOREIGN KEY (product_id) REFERENCES Products(product_id)  
          );
          

          基本应用

          ---

          当我们查询每个区域的销量时，可以使用如下CTE语句

          with region_sales as (
            SELECT region, SUM(quantity) AS total_sales
              FROM orders
              GROUP BY region
          )
          select * from region_sales;
          

          在这个例子中，CTE查询语句从基本表orders中得到每个区域的销量，在主查询中就可以直接引用CTE临时表了。

          多个CTE

          ---

          可以包含多个CTE语句，也就是CTEs，而且CTE之间也可以进行引用，递进的进行查询。

          并行的 CTE

          当我们想要查询销量最好的区域和销量最好的产品，那么可以用下面的语句。

          with region_sales as (
            SELECT region, SUM(quantity) AS total_sales
              FROM orders
              GROUP BY region order by total_sales desc;
          ),
          procduct_sales as (
            select products.product_name, sum(quantity) as total_sales 
              from orders, products 
              where orders.product_id = products.product_id
              group by orders.product_id,products.product_name  order by total_sales desc;
          )
          select (select  region from region_sales limit 1) as top_region, 
                 (select product_name from procduct_sales limit 1) as top_product ;
          

          • 分别用两个CTE查询得到区域销量的排名和产品销量的排名；
          • 在主查询中，得到区域销量第一和产品销量的第一；

            递进的 CTE

            不同的CTE之间还可以引用，下面我们计算销售额最高的区域来演示一下：

            with region_amount as (
              select region, orders.quantity * products.price as amount from orders, products where orders.product_id = products.product_id 
            ),
            top_region as 
            (
              select region, sum(amount) total_amount from region_amount group by region
            )
            select * from top_region order by total_amount desc;
            

            • 第一个CTE查询得到按区域分类的各产品销售额，但是区域有重复行；第二个CTE进一步进行计算，按区域计算得到销售总额；
            • 主查询得到全部区域的一个销售总额排序；

              递归查询

              ---

              有些时候，我们需要递归处理一些查询，此时就要用到WITH RECURSIVE这个关键字，它的SQL语句形式如下：

              WITH RECURSIVE cte_name (column1, column2, ...)
              AS(
                  -- 非递归项
                  SELECT select_list FROM table1 WHERE condition
                  UNION [ALL]
                  -- 递归语句
                  SELECT select_list FROM cte_name WHERE recursive_condition
              ) 
              SELECT * FROM cte_name;
              

              递归的with语句形式与上面的非递归类型，只是递归的CTE语句中包含两部分：

              • 非递归项，这是第一次递归的结果，也就是起始值；
              • 递归项，开始n次递归调用，它调用CTE自身；直到查询返回值为空时，结束递归；并将n次查询结果通过union或union all合并形成最终结果集；
              • 前两者通过 union 或 union all进行合并，其中union的结果集会去掉重复的行；

                递归的过程中，会产生临时表，存放本次递归的结果，下次递归时就从这个表进行查询，当表为空时就停止递归。

                简单递归查询

                下面我们来看一个简单的例子；

                计算1到100的和，这是经常循环改递归的一个算法，这里用CTE查询来实现如下：

                WITH RECURSIVE t(n) AS (
                    VALUES (1)
                  UNION ALL
                    SELECT n+1 FROM t WHERE n < 100
                )
                SELECT sum(n) FROM t;
                

                当然它最常用的是进行图的遍历递归或者树的遍历递归查询。

                总结

                ---

                本文主要分享了CTE语句的基础用法，包括单个CTE，多个CTE，CTE之间引用，以及CTE中的递归用法，熟悉CTE的用法之后，对于之前复杂的SQL就可以改造成多个CTE的形式，更加易于理解和维护，大家可以尝试一下。

                结尾

                非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！

                作者邮箱：study@senllang.onaliyun.com

                如有错误或者疏漏欢迎指出，互相学习。

                注：未经同意，不得转载！

                 
                 西安网站建设哪家便宜  网站搜索引擎优化案例  自己创建个人免费网站  深圳网络推广推荐  成都建设网站哪家公司好  如何编写网站建设方案书