在 SQL Server 中使用 CTE 将字段值连接到一个字符串

引言

在学习 SQL Server 2005 的新功能，即 CTE 时，我想到可以使用它将一个字段的值连接到一个字符串中，因为它可用于递归工作。在开始示例之前，让我先描述一下 CTE。

CTE 或公共表表达式是 MS SQL Server 2005 中提供的新构造。它基本上是一个临时视图，可以在 SELECT 语句中用于查询数据。大多数情况下，我们需要编写复杂的查询，其中涉及在单个查询中多次使用的子查询。 在这种情况下，我们可以使用 CTE 并在查询中根据需要多次引用它。这简化了查询的逻辑，并使其更易于维护。

创建 CTE 的语法是

-- Defining a CTE

;WITH SalesmanCTE(SalesmanId, SalesmanName)
AS
(
    SELECT SalesmanId, Name FROM Salesman
)


-- Refering a CTE in query

SELECT * FROM SalesmanCTE

CTE 的使用方式非常类似于在数据库中创建的普通视图，因此我们可以直接将一些复杂的查询（可能需要用作子查询）嵌入到 CTE 中，并在我们的查询中引用该 CTE。我们可以使用任何类型的 join、where 子句或其他可与普通表或查询一起使用的构造。例如，假设我们需要编写一个像这样的查询

SELECT SalesmanName
FROM Salesman 
INNER JOIN (SELECT SalesmanId, MAX(Sale) FROM Sales GROUP BY SalesmanId) A
ON Salesman.SalesmanId = A.SalesmanId

可以使用 CTE 轻松编写此查询

;WITH SalesmanCTE (SalesmanId, MaxSale)
AS
(
  SELECT SalesmanId, MAX(Sale) 
  FROM Sales 
  GROUP BY SalesmanId
)

SELECT SalesmanName
FROM Salesman 
INNER JOIN SalesmanCTE A
ON Salesman.SalesmanId = A.SalesmanId

在第二个示例中，子查询已使用 CTE 修改为单独的视图，并在主查询中使用，使其易于理解。 CTE 还有另一个很好的递归调用功能，即 CTE 可以递归调用自身以返回分层数据。例如，如果您有一个递归性质的表，例如具有自引用外键约束以表示 n 级类别（我们在购物车等中看到的）的类别。在这里，为了获得类别的所有子类别（最多 n 级），我们可以使用 CTE。有关如何递归使用它的更多信息可以在这里找到。

我在本文中使用 CTE 的相同递归性质，将行的值连接为以逗号分隔的值到一个列中。下面给出了一个基本示例。

使用代码

为此，我使用了以下表格

tblTest
------------------
FId INT
FName VARCHAR(10)

表格中的值是

FId FName
--- ----
2    A
4    B
5    C
6    D
8    E

SQL

;WITH ABC (FId, FName) AS
(
    SELECT 1, CAST('' AS VARCHAR(8000)) 
    UNION ALL
    SELECT B.FId + 1, B.FName +  A.FName + ', ' 
    FROM (And the above query will return
SELECT Row_Number() OVER (ORDER BY FId) AS RN, FName FROM tblTest) A 
    INNER JOIN ABC B ON A.RN = B.FId 
)
SELECT TOP 1 FName FROM ABC ORDER BY FId DESC

上面的查询将返回

FName
----------------------------
A, B, C, D, E,

在 CTE 中，第一个查询首先运行，第二个查询递归运行，以将字段值连接到公共字符串字段中。 最后一个查询仅显示 CTE 的结果集中最后一行。

我现在要解决一个更实际的问题，可以使用它来解决。 此示例的建议由 Ashaman 提出，他是第一个评论本文的人。 因此，以该示例为例，我将采用三个表格，如下面的关系图所示。

Salesman 表包含为公司工作的销售员的姓名。 Area 表保存产品销售的区域，而 SalesmanArea 则保存有关哪些销售员在哪个区域下工作的信息。 销售员可以在多个地点工作。 现在，假设我们有一个要求，我们要显示所有销售员的姓名以及由他们主管的以逗号分隔的区域列表。 为了获得这样的结果，我们可以使用 CTE 的递归特性，查询将是

;WITH AreaCTE (RowNumber, SalesmanId, AreaName, Areas) AS
(
  SELECT 1, SA.SalesmanId, MIN(AR.AreaName), CAST(MIN(AR.AreaName) AS VARCHAR(8000)) 
  FROM SalesmanArea SA
  INNER JOIN Area AR ON SA.AreaId = AR.AreaId
  GROUP BY SalesmanId

  UNION ALL

  SELECT CT.RowNumber + 1, SA.SalesmanId, AR.AreaName, CT.Areas + ', ' + AR.AreaName 
  FROM SalesmanArea SA 
  INNER JOIN Area AR ON SA.AreaId = AR.AreaId
  INNER JOIN AreaCTE CT ON CT.SalesmanId = SA.SalesmanId 
  WHERE AR.AreaName > CT.AreaName
)

SELECT A.SalesmanId, S.Name, Areas 
FROM AreaCTE A
INNER JOIN Salesman S ON S.SalesmanId = A.SalesmanId
INNER JOIN (SELECT SalesmanId, MAX(RowNumber) 
AS MaxRow FROM AreaCTE GROUP BY SalesmanId) R
ON A.RowNumber = R.MaxRow AND A.SalesmanId = R.SalesmanId
ORDER BY SalesmanId

我的表格包含的记录是

上面查询的结果是

这为我提供了所需的结果，其中包含销售员的姓名以及他活跃的区域的以逗号分隔的列表。 当我们编写递归 CTE 时，我们需要提供两个使用 UNION ALL 连接在一起的查询。 第一个查询称为锚查询/成员，第二个查询称为递归查询/成员。 首先，第一个查询被触发，其结果被第二个查询用来生成其结果，因为第二个查询正在引用 CTE 本身。 这样，CTE 调用自身以提供数据的递归处理。

希望这有助于您了解 CTE。

历史

1. 使用基本示例创建文章。
2. 添加了有关 CTE 的更多信息。
3. 根据建议添加了销售员和区域问题的实际示例。