SQL WITH子句

系统运维 2024-03-31 泡泡手机阅读

当您深入研究SQL和数据库查询时，您将遇到的最强大和最令人难以置信的特性之一是公共表表达式，它通常被称为cte。

在SQL中，WITH子句也称为CTE。这是一个强大的功能，使我们能够在查询中创建临时结果集。cte的一个主要作用是将复杂查询简化为更小且可重用的子查询。从长远来看，这有助于使代码更具可读性和可维护性。

在本教程中，我们将使用WITH子句和支持的功能来探索公共表表达式的工作原理。

要求:

为了演示，我们将使用以下内容:

MySQL 8.0及以上版本
Sakila样本数据库

满足了给定的需求后，我们可以继续学习有关cte和With子句的更多信息。

SQL WITH子句

WITH子句允许我们定义一个或多个临时结果集，这些结果集称为公共表表达式。

我们可以像引用其他表或结果集一样在主查询中引用结果cte。这在创建模块化SQL查询中起着至关重要的作用。

虽然CTE的语法可能会根据您的需求略有不同，但以下显示了SQL中CTE的基本语法:

WITH cte_name (column1, column2，…)(

——CTE查询

选择……

从…

在那里……

）

——主查询

选择……

从…

JOIN cte_name ON…

在那里……

我们从with关键字开始，它告诉SQL数据库我们希望创建和使用CTE。

接下来，我们为CTE指定名称，以便在其他查询中引用它。

如果CTE包含列别名，我们还指定一个可选的列名称列表。

接下来，我们继续定义CTE查询。这包含了CTE在一对括号中执行的所有任务或数据。

最后，我们指定引用CTE的主查询。

使用示例:

了解如何使用和使用cte的最佳方法之一是查看一个实际示例。

以Sakila示例数据库为例。假设我们希望找到租赁次数最多的前10位客户。

看看下面显示的CTE。

使用SQL WITH子句查找租金最高的前10个客户:

客户租金为(

SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS rental_count

来自客户c

JOIN rental r ON c.customer_id = r.customer_id

GROUP BY c.customer_id, c.first_name, c.last_name

）

SELECT *

从CustomerRentals

按rental_count排序

限制10;

在给定的示例中，我们首先使用WITH关键字定义一个新的CTE，后面跟着我们希望分配给CTE的名称。在本例中，我们称之为“CustomerRentals”。

在CTE主体中，我们通过连接客户和租金表来计算每个客户的租金计数。

最后，在主查询中，我们从CTE中选择所有列，根据租赁计数(降序排列)对结果排序，并将输出限制为前10行。

这使我们能够获取租赁次数最多的客户，如下所示:

递归cte

在其他一些情况下，您可能要处理分层数据结构。这就是递归cte发挥作用的地方。

让我们举一个例子，我们想要导航分层组织或表示树状结构。我们可以使用WITH RECURSIVE关键字来创建递归CTE。

由于Sakila数据库中没有可以用来演示递归CTE的分层数据，因此让我们设置一个基本示例。

创建表

department_id INT主键AUTO_INCREMENT;

department_name VARCHAR(255) NOT NULL，

parent_department_id INT,

外键(parent_department_id)引用department(department_id)

）;

INSERT INTO department (department_name, parent_department_id)

值

(“公司”,零),

(1)“融资”

(1)“人力资源”

(2)“会计”

“招聘”,3),

(“工资”,4);

在本例中，我们有一个带有一些随机数据的示例“department”表。为了找到部门的层次结构，我们可以使用递归CTE，如下所示:

递归部门层次结构AS (

SELECT department_id, department_name, parent_department_id

从部门

如果parent_department_id为NULL

UNION ALL

SELECT d.department_id, d.department_name, d.parent_department_id

来自d部门

JOIN DepartmentHierarchy dh ON d.parent_department_id = dh.department_id

）

SELECT *

从DepartmentHierarchy;