SQL CTE通用表达式

通用表达式的目的是解决子查询的一些限制。它还提供了一种查询相关数据项集合的方法，这些数据项通过层次关系（如组织层次结构）相互关联。

什么是CTE（Common Table Expression）

CTE是一次性结果集，也就是临时表，仅在查询的持续时间内存在。它允许我们在单个SELECT、UPDATE、INSERT、DELETE、CREATE、VIEW或MERGE语句的执行范围内引用数据。CTE是临时的，因为它不能存储在任何地方；一旦查询执行完毕，它可以尽快被释放。

它最早在SQL Server 2005中出现。数据库管理员一直更喜欢使用CTE作为子查询/视图的替代方法。它们遵循ANSI SQL 99标准，并且符合SQL规范。

我们为什么需要CTE

CTE可以通过使复杂查询更易于管理和编写，使其更易读和简单，类似于数据库视图和派生表。我们可以通过将复杂查询分解为简单块来重用或重新编写查询。

语法

下面是CTE的语法：

WITH CTE_NAME (column_name) AS (query)
SELECT * FROM CTE_NAME;

CTE语法由CTE名称、定义列表达式的语句查询和可选的列列表组成。定义后，我们可以将其用作SELECT、INSERT、UPDATE、DELETE和merge查询中的视图。

以下图示是查询定义的表示形式：

SQL CTE通用表达式

注意 − 在编写CTE查询定义时，我们可以使用以下命令。

ORDER BY，除非还使用TOP子句。
INTO
Option子句
FOR BROWSE

示例

在以下示例中，让我们看看CTE在SQL Server中的工作原理。在这种情况下，我们将使用客户表并对其执行CTE。

以下是客户表的内容 –

+------+----------+------+-----------+--------+
| ID   | NAME     | AGE  | ADDRESS   | SALARY |
+------+----------+------+-----------+--------+
|    1 | Ramesh   |   32 | Ahmedabad |   2000 |
|    2 | Aman     |   23 | Ranchi    |  40000 |
|    3 | kaushik  |   23 | Kota      |   2000 |
|    4 | Chaitali |   25 | Mumbai    |   6500 |
+------+----------+------+-----------+--------+

以下是使用CTE提取详细信息的查询-

WITH customer_AGE
AS (SELECT * FROM customers WHERE AGE = 23)
SELECT ID, NAME, AGE FROM customer_AGE;

输出

由于我们创建了一个名为customer AGE的公共表达式，当上述查询被执行时，我们从CTE接收到一个ID，姓名和年龄，而该年龄等于23。

+------+---------+------+
| ID   | NAME    | AGE  |
+------+---------+------+
| 2    | Aman    |   23 |
| 3    | kaushik |   23 |
+------+---------+------+

多个通用表达式（CTE）

多个CTE可用于联接在一起；这简化了复杂的SQL查询。每个复杂查询都有自己的CTE，可以在with子句的外部引用和联接。

假设我们有一些情况需要编写多个查询并将它们联接在一起以查看结果；在这种情况下，我们可以使用多个CTE。我们必须使用逗号运算符，后跟CTE名称，将多个CTE查询分隔开并合并为一个单独的语句。

语法

以下是多个CTE的SQL查询的语法 −

WITH
   CTE_NAME1 (column_name) AS (query),
   CTE_NAME2 (column_name) AS (query)
SELECT * FROM CTE_NAME1
UNION ALL
SELECT * FROM CTE_NAME2;

注意 - 多个公共交通企业可以用于UNION、UNIONALL、JOIN、INTERSECT或EXCEPT。

示例

在这个示例中，我们定义了两个命名为customers_in_Ranchi和customers_in_Kota的公共交通企业。然后，这些公共交通企业的子查询的结果集填充了公共交通企业。最后，我们将在查询中使用公共交通企业的名称，该查询将返回所有位于Ranchi和Kota的客户。

以下是客户表：

+---+-------+---------+----+--------+
|ID |NAME   |ADDRESS  |AGE |SALARY  |
+---+-------+---------+----+--------+
|1  |Rakesh |kota     |24  |30000   |
+---+-------+---------+----+--------+
|2  |Aman   |Ranchi   |23  |50000   |
+---+-------+---------+----+--------+
|3  |Vivek  |kota     |22  |34000   |
+---+-------+---------+----+--------+
|4  |Akash  |Ranchi   |22  |40000   | 
+---+-------+---------+----+--------+
|5  |Rahul  |Hyderabad|23  |38000   |
+---+-------+---------+----+--------+
|6  |Raja   |Patna    | 28 |60000   |
+---+-------+---------+----+--------+
|7  |Sumit  |Ranchi   |25  |40000   |
+---+-------+---------+----+--------+

以下是用于获取详细信息的SQL CTE查询：

WITH
   customers_in_Ranchi AS (SELECT * FROM customers WHERE ADDRESS = 'Ranchi'),
   customers_in_kota AS (SELECT * FROM customers WHERE ADDRESS = 'Kota')
SELECT ID, NAME, ADDRESS FROM customers_in_Ranchi
UNION ALL
SELECT ID, NAME, ADDRESS FROM customers_in_Kota;

输出

以下是使用多个CTE（通用表表达式）显示客户的ID、姓名和地址的输出表格。

+----+-------+---------+
| ID | NAME  | ADDRESS |
+----+-------+---------+
| 2  | Aman  | Ranchi  |
| 4  | Akash | Ranchi  |
| 7  | Sumit | Ranchi  |
| 1  | Rakesh| kota    |
| 3  | Vivek | kota    |
+----+-------+---------+

递归CTE

通用表达式是一个查询，它在返回空结果之前持续引用先前的结果或自身。当运行时，递归查询会在数据的子集上连续迭代。它只是定义了自引用的查询。

递归CTE只有在具有UNION ALL语句和使用CTE本身的查询的第二个定义时才能递归。

示例

在以下SQL查询中，我们了解CTE如何在SQL Server中递归工作。考虑下面的语句，它生成了前五个偶数的系列：

WITH even_no_CTE(ID, N) AS
(
   SELECT 1, 2
   UNION ALL
   SELECT ID+1, N+2 FROM even_no_CTE WHERE ID<5
)
SELECT * FROM even_no_CTE;

输出

随后的表格递归地检索偶数，直到ID等于5为止。

+----+-----+
| ID |  N  |
+----+-----+
| 1  |  2  |
| 2  |  4  |
| 3  |  6  |
| 4  |  8  |
| 5  |  10 |
+----+-----+

示例

在下面的示例中，我们从2023年1月1日到2023年1月10日期递归显示日期。

以下是显示日期的查询：

DECLARE @startDate date = '2023-01-01';
Declare @endDate date = '2023-01-10';
WITH RecursiveCte AS
(
   SELECT @startDate AS OrderDate
   UNION ALL
   SELECT DATEADD(d, 1, OrderDate) FROM RecursiveCte
   WHERE DATEADD(d, 1, OrderDate)<= @endDate
)
SELECT * FROM RecursiveCte;

输出

当我们执行上述递归SQL查询时，我们会得到以下日期 –

+------------+
| OrderDate  |
+------------+
| 2023-01-01 |
| 2023-01-02 |
| 2023-01-03 |
| 2023-01-04 |
| 2023-01-05 |
| 2023-01-06 |
| 2023-01-07 |
| 2023-01-08 |
| 2023-01-09 |
| 2023-01-10 |
+------------+

示例

在下面的例子中，我们创建了一个递归CTE，它将根据递归CTE的参数从顾客表中获取所有的数据。

WITH recursive_cust (ID, NAME, ADDRESS, AGE) AS 
(
   SELECT ID, NAME, ADDRESS, AGE
   FROM customers
   WHERE SALARY < 40000
   UNION ALL
   SELECT ID, NAME, ADDRESS, AGE
   FROM customers
   WHERE AGE < 25
)
SELECT * FROM recursive_cust;

输出

执行以上SQL查询时，将递归显示所有年龄小于25或工资小于40000的客户表中的数据。

+-----+-------+----------+-----+
| ID  | NAME  | ADDRESS  | AGE |
+-----+-------+----------+-----+
| 1   | Rakesh| kota     | 24  |
| 3   | Vivek | kota     | 22  |
| 5   | Rahul | Hyderabad| 23  |
| 1   | Rakesh| kota     | 24  |
| 2   | Aman  | Ranchi   | 23  |
| 3   | Vivek | kota     | 22  |
| 4   | Akash | Ranchi   | 22  |
| 5   | Rahul | Hyderabad| 23  |
+-----+-------+----------+-----+