SQL Wizardry 第三部分 - 通用表表达式 (CTE)

• 下载 SQL_Wizardry_03.zip - 1.3 KB

引言

欢迎阅读我关于公用表表达式（或 CTE）的文章。公用表表达式是一种对复杂的 SQL 进行分段以提高可读性的绝佳方法，但它的作用远不止于此。让我们开始吧。

背景

如果您没有阅读我之前的文章，您可能不知道我用 AdventureWorks 数据库来举例。我今天也会用其他例子，但如果您的电脑上安装了 AdventureWorks，仍然会有帮助。您可以在 这里 获取。

示例 1 – 计算两个平均值。

从我之前的文章中，您应该知道这个查询的作用。

select sum(unitprice) as totalOrders, orderdate from sales.salesorderdetail sod 
inner join  sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
group by orderdate
order by orderdate desc

这将为您提供每天的订单总数，按日期排序。但是，如果您想知道每月的平均值呢？

select avg(unitprice) as averageOrders, month(orderdate), year(orderdate) from sales.salesorderdetail sod 
inner join  sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
group by year(orderdate), month(orderdate)
order by year(orderdate), month(orderdate)

很简单，对吧？现在，如果您想显示月平均值以及年平均值以进行比较，该怎么办？这意味着您的 GROUP BY 不再适用，您需要按年份分组以获得年平均值，按月份分组以获得月平均值。那么，您该怎么办？CTE 来帮忙了！

首先，让我们通过使用 CTE 来重现上面的查询来阐述 CTE 的概念。

with averages
as
(
select avg(unitprice) as averageOrders, month(orderdate) as month, year(orderdate) as year from sales.salesorderdetail sod 
inner join  sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
group by year(orderdate), month(orderdate)
)

select * from averages order by year, month

实际上，CTE 在这里并没有增加任何新功能。但是，我们可以从这段代码中获得一些重要的要点：1 – 我必须在 SQL 中为我的所有列指定别名，以便 CTE 能够工作。2 – 我无法在 CTE 中进行 ORDER BY，必须在最终的 SELECT 语句中进行。

事实上，这也是有效的。

with averages(averageOrders, month, year)
as
(
select avg(unitprice), month(orderdate), year(orderdate) from sales.salesorderdetail sod 
inner join  sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
group by year(orderdate), month(orderdate)
)

select * from averages order by year, month

这引出了一个重要的观点。CTE 内部列的名称无关紧要，如果您在 CTE 的顶部指定了名称。换句话说，如果我这样做：

with averages(averageOrders, year, month)
as
(
select avg(unitprice), month(orderdate) as month, year(orderdate) as year from sales.salesorderdetail sod 
inner join  sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
group by year(orderdate), month(orderdate)
)

select * from averages order by year, month

您会发现顺序被打乱了，列名也错了。一旦您在 CTE 的顶部指定了名称，就会使用这些名称。唯一的规则是，您必须提供与列数相同数量的别名，然后它们会从左到右应用，而不会考虑 CTE 中 SQL 返回的列名。如果您处理大型 CTE 并添加列，这可能会让您陷入困境。我建议尽量避免在 CTE 顶部提供别名，因为这是无意中引入 bug 的一个好地方。

所以，这没用了，对吧？如果我们还想显示年平均值怎么办？当我们想计算几个不兼容的总计时，我们会为每个使用一个 CTE，然后在最后将它们连接起来。如下所示：

with averagesByMonth  
as
(
select avg(unitprice) as averageOrders, month(orderdate) as month, year(orderdate) as year from sales.salesorderdetail sod 
inner join  sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
group by year(orderdate), month(orderdate)
),
averagesByYear as
(
select avg(unitprice) as averageOrders, year(orderdate) as year from sales.salesorderdetail sod 
inner join  sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
group by year(orderdate)

)

select m.AverageOrders as AverageByMonth, y.AverageOrders as AverageByYear, m.Month, m.year  from averagesByMonth m
inner join averagesByYear y on m.year = y.year
 order by m.year, m.month

您还可以通过 CTE 来简化计算的 SQL。例如，如果您想按销售代表显示平均值，您只需从 CTE 中返回销售代表 ID，以便与您的 SELECT 语句绑定，该语句还会选择代表姓名和位置等信息（这需要进一步在 AdventureWorks 中进行连接）。这既可以防止因连接静默排除值而导致的错误，也可以使您的代码更具可读性。我将其视为一种面向对象（仅仅因为我的思维方式如此）。它允许我定义一个对象的职责是计算一件事，然后我可以在其他代码中重用该对象。

另一个例子

我收到了很多关于在我的文章中创建表等的完整 SQL 的请求，所以这次我将这样做。这个例子是基于我们在论坛上提出的一个问题的。如果您的表看起来像这样：

create TABLE students(studentid INT IDENTITY(1,1), professorid INT, lectureid INT)

这会将学生与讲座和教授联系起来。我认为表设计是有缺陷的。讲座应该单独存储将由哪个教授授课。不过，我们现在先用这个例子。

所以，这个人想要的是一份所有学生的名单，按照每位教授在所有讲座中看到的平均学生数量排序。换句话说，就是按学生数量除以讲座数量的平均值排序。为了做到这一点，您需要按教授对查询进行分组，这会给您带来组，而不是单个记录。同样，解决方案是 CTE。所有这些代码都在下载文件中，但这是插入表值的代码：

INSERT INTO students (professorid, lectureid)
VALUES(101, 401), (101, 402),(101, 402),(101, 402),
(101, 401), (101, 402),
(102, 403), (102, 404),(102, 404),(102, 404),(102, 404),(102, 404),(102, 404),(102, 404),
(103, 405), (104, 406),
(101, 406),(101, 401),(101, 406),(101, 406),(101, 406),(101, 406),(101, 406),
(103, 406),(103, 406),(103, 406),(103, 406),(103, 408),(103, 408),(103, 408),(103, 408),(103, 408),(103, 408),(103, 408),(103, 408),
 (102, 408),(102, 408),(102, 408), (103, 405), (103, 405), (103, 405), (103, 405) 

这会创建 42 行。获取平均值列表的 SQL 如下：select professorId, count(studentid)/count( distinct lectureid) as average from students group by professorId

它返回这个：

professorId	average
101	4
102	3
103	5
104	1

所以有四位教授，42 名学生，在所有讲座中的平均学生人数分别为 1、3、4 和 5。现在我们将其封装在 CTE 中，并使用 CTE 来对主表中的列表进行排序，如下所示：

with avgStudents as
(
 select professorId, count(studentid)/count( distinct lectureid) as average from students group by professorId
)
 
select s.studentid, av.professorid, s.lectureid, av.average from students s
inner join avgStudents av on av.professorId = s.professorId
order by av.average desc

基本概念是，使用 CTE 计算您需要的值，并用一个主键返回它们，您可以使用该主键将值连接回您将进行主 SELECT 操作的主表。

递归

到目前为止，我们看到的一切都方便且有助于编写可读的查询。我总是喜欢说，编写编译器能理解的代码很容易。诀窍是编写人类能理解的代码。但是，CTE 能否做一些否则不可能的事情？答案是可以。CTE 允许您编写递归查询（即，一遍又一遍地调用自身的查询，直到达到终止点）。

我在网上看到了很多关于这是如何工作的例子。一个常见的例子是展示公司中的组织结构图。我决定用另一个常见的例子，即论坛帖子树。如果您查看本文的底部，或者在本网站的论坛上，您会看到您可以回复任何帖子，并且它们会显示为一棵树。虽然我确信实际的 SQL 更智能，但这通常在示例中以类似的结构显示：

create table postHistory
(
	id int,
	parentid int
)

思路很简单——每个帖子都知道它的父帖子是谁。如果父级为 null，则它是一个顶层帖子。当然，一个真实的帖子还应该有正文、标题，以及发布者的 id（可能还会链接到另一个表来存储谁给该帖子投票，以及他们投了什么票）。问题来了，如何获得构成单个帖子树的帖子列表？您可以编写这样的 SQL：

declare @id int = 1

Select * from posthistory p1
Inner join posthistory p2 on p2.parentid = p1.id 
Inner join posthistory p3 on p3.parentid = p2.id 
where p1.id = @id

但是，您需要为每个级别添加自连接，而且这也很昂贵，因为每次自连接都会创建一个新的昂贵连接，成本呈指数级增长。这不可维护。这时就轮到 CTE 了。思路很简单。我们创建一个 CTE，并在其中创建一个 UNION ALL，该 UNION ALL 通过名称引用 CTE 本身，从而导致其调用自身。这是 SQL：

declare @id int = 1;

with postlist as
(
 select 1 as level, id, parentid from postHistory where id = @id 
  union all
 select pl.level + 1 as level, ph.id, ph.parentid from postHistory ph
 inner join postlist pl on ph.parentid = pl.id 
)

select * from postlist order by level, id

所以，从顶部开始，我们将搜索一个特定的帖子，因此我们定义该帖子的 id。然后我们定义我们的 CTE，称为 postlist。

第一层看起来像这样：

select 1 as level, id, parentid from postHistory where id = @id 

select 1 as level 是一个锚点。它定义了我们要返回的分层级别字段的顶层。此字段将告诉我们每个帖子的级别，并允许我们按级别排序。

这将只返回顶层帖子。这是我们树中的顶层记录，我们想要返回的一切都基于从这一级别向下遍历树。CTE 的其余部分如下：

  union all
 select pl.level + 1 as level, ph.id, ph.parentid from postHistory ph
 inner join postlist pl on ph.parentid = pl.id 

递归 CTE 要求我们使用 UNION ALL 而不是 UNION。第一个 SELECT 语句会增加级别。这或许是我们递归工作方式最清晰的示例。然后，我们将 postHistory 表的一个新实例连接到 postlist（这是我们的 CTE 名称）。这会产生 CTE 的完整部分作为连接的第二部分，从而调用自身。因为我们只想遍历到末尾，所以第二个 SELECT 语句中没有 WHERE 子句。如果您执行此操作，您将看到类似这样的结果：

level	id	parentid
1	1	NULL
2	3	1
2	4	1
3	5	3
3	6	4
3	7	3
3	8	3
3	9	4
4	10	5
4	11	5
4	13	5
4	14	6
5	12	10
5	15	14
5	16	14

正如您所见，这已经调用了自身，一直遍历到树的底部。一如既往，如果我们需要（例如，总帖子投票数，如果存在的话），我们可以将 CTE 结果与表连接起来以提取其他信息。您可以将 @id 的值更改为 2，以查看它如何选择另一个树。

一个更规范的例子是选择所有层级，尽管在这种情况下，它与 select * 相同。

with postlist as
(
 select 1 as level, id, parentid from postHistory where parentid is null
  union all
 select pl.level + 1 as level, ph.id, ph.parentid from postHistory ph
 inner join postlist pl on ph.parentid = pl.id
 where ph.parentid is not null
)

select * from postlist order by parentid

一个更合理的做法是按顺序列出每个帖子，如下所示：

with postlist as
(
 select id as root, id, parentid from postHistory where parentid is null
  union all
 select pl.root as root, ph.id, ph.parentid from postHistory ph
 inner join postlist pl on ph.parentid = pl.id
 where ph.parentid is not null
)

select * from postlist order by root, id

这里传递的值没有增加，这意味着对于每组递归步骤，都保留了顶层值，并用于依次对每个帖子树进行排序。假设每个帖子 id 都是顺序的，在现实世界中，可能存在一个日期列来确保这种情况。

最终注释

• 如果您将 CTE 查询与其他 SQL 一起使用，在 CTE 之前最后一个以 ; 结尾的语句，并且 CTE SQL 后面也有内容，那么它也需要以 ; 结尾。因此，许多 CTE 示例都以 ; 开头，因为它要么无害，要么使 CTE 可以与前面的 SQL 一起工作。
• 您不能多次使用 CTE，您可以拥有任意数量的 CTE，但它们都必须在一个查询中使用。
• CTE 可能比临时表更高效，但临时表可以有索引，而 CTE 不能。
• 在一个 SQL 查询中多次使用的 CTE 可能会使优化器感到困惑并导致 SQL 变慢。如果您在一个 SQL 查询中两次使用相同的 CTE 名称并且速度很慢，请尝试声明两个相同的 CTE 并各使用一次。
• CTE 可以使 SQL 看起来比实际更整洁。如果您使用 CTE 编写子查询，它会减慢速度，就像您内联编写它一样。然而，通常您可以使用 CTE 来避免子查询，而是连接到包含子查询结果的 CTE，并使用主键进行连接。

希望这为您打开了 SQL 的新世界。学习 CTE 肯定为我打开了这一扇门，它让那些看起来非常复杂的查询变得超级简单，并且使您的代码更具可读性，因此也更易于维护。

对于下周的条目，我打算撰写关于如何使用 CSV、XML 或用户定义表类型将任意值列表传递给 SQL Server 的内容。