不寻常的通用表表达式 (CTE)

公共表表达式 (CTE) 随 SQL 2005 一起引入。它们是一种强大的新构造，用于构建高级查询。

Microsoft CTE 文章 Microsoft CTE Article 描述了 CTE 的 4 种主要用途：

• 递归查询
• 内联视图
• 按派生列分组
• 重用同一张表

令我惊讶的是，在新的 SQL 平台上，许多人实际上并不熟悉或不使用 CTE。我将分享两个快速而粗糙的示例，以展示它们的强大功能。

分层数据

我过去写过一些关于分层数据的内容（阅读 Silverlight 中的分层数据模板 了解示例）。使用 CTE，SQL 可以更轻松地以分层方式检索数据。

假设您有一个内容管理网站，其中包含文章，并且文章可以分组到分层类别中（即，一个类别可能包含其他类别）。有几种方法可以解决这个问题，但一种方法是让主类别表被另一个封装关系的表引用，如下所示：

我们将从一个“文章”部分开始，其中“白皮书”是其子类别，它还包含“技术相关”作为子类别，然后添加一个“常见问题解答”，其中“使用网站”和“注销”是其子类别。我们的主表如下所示：

CategoryID	类别
1	文章
2	常见问题解答
3	白皮书
4	使用网站
5	注销
7	技术相关

为了显示关系，我们将像这样将记录插入 CategoryLevel 中：

Parent	Child
1	3
2	4
2	5
3	7

一个现实世界的挑战是在用户查看技术文章时显示面包屑导航。给定子节点（7，技术相关），我们如何递归遍历层次结构以显示所有父节点？

使用公共表表达式，我们将别名查询的“顶部”（当前类别的选择），然后将其连接以递归遍历树，如下所示：

with RecursiveCat(CategoryID,Category) 
 as (
  select
    CategoryID,
    Category
  from dbo.Categories
  where CategoryID = 7
  union all 
  select
    L.ParentCategoryID as CategoryID, 
    Categories.Category 
  from dbo.CategoryLevels L 
  join RecursiveCat c 
    on c.CategoryID = L.ChildCategoryID
  inner join dbo.Categories 
    on L.parentCategoryID = Categories.CategoryID
 )
 select * from RecursiveCat

这里的关键是注意“union all”与 join。顶层查询“启动”它并选择目标节点。union 描述了一个递归模板……在内部，我们引用描述父子关系的 CategoryLevels 表，并将其连接到外部的公共表表达式 (CTE)。这意味着对于每个记录，它都会连接回自身并“展开”，直到完全暴露层次结构。

运行上述查询将得到：

CategoryID	类别
7	技术相关
3	白皮书
1	文章

当它沿着树向上移动时。

排名数据

另一个最常见的用途是排名数据。让我们以上面的示例为基础，声明一个 articles 表：

文章仅属于某个类别，并具有发布日期。我暂时省略了标题、正文等详细信息。

在内容网站上展示文章的一种非常常见的方式是显示发布到给定类别的最新文章。假设您有几十篇文章，最简单的方法是什么？

当然，如果您很务实，可能会在 Categories 表上放置一个 MostRecentArticleID 列，甚至引入一个新表来保存它。但是，如果您想玩得开心（并让我继续我的借口来提供示例），您可以使用排名函数（我们还没有到公共表表达式，所以请忍耐）。现在，尽量忽略我的拼写错误，因为 ArticlePublishData 应该改为 ArticlePublishDate。第一个查询将让您了解 RANK 的工作原理：

SELECT CategoryID, ArticleID, ArticlePublishData,
     ROW_NUMBER() OVER (PARTITION BY CATEGORYID ORDER BY ArticlePublishData DESC) As "MyRank"
   FROM Articles 

当我们运行此查询时，我们将获得所有文章，但如果您注意到，“MyRank”列在每个类别中都会重置。在类别内，它从最新的日期开始，值为“1”，然后递增。这是因为我们正在按类别进行分区，或者告诉 CTE 重置排名，然后在这些类别内按发布日期排序。在此示例中，1 和 2 属于同一类别，然后 3 属于一个新类别。

CategoryID	ArticleID	ArticlePublishData	秩
1	2	2008-12-13 00:00:00:00	1
1	1	2006-12-14 00:00:00:00	2
2	3	2009-01-02 00:00:00:00	1

现在，只需添加一个使用公共表表达式的过滤器即可。我们将排名函数别名为提取行，然后按行进行筛选：

;with CTE(CategoryID, ArticleID, ArticlePublishData, Row) AS (
SELECT CategoryID, ArticleID, ArticlePublishData,
     ROW_NUMBER() OVER (PARTITION BY CATEGORYID ORDER BY ArticlePublishData DESC) As "MyRank"
   FROM Articles) 
SELECT * from CTE
where Row = 1

这将只返回每个类别的最新文章。

希望这已为您提供了对公共表表达式有用、快速且简单的介绍。只需在网上搜索该短语，您就会找到许多示例和教程，帮助您将 SQL 技能提升到一个新的水平。

codeproject