注意我们学习过的子查询，一个很明显的特点：子查询只能返回“一列”的结果集。那如果我想返回多列呢？这就需要使用：

表表达式（Table Expression）

又被称之为“表子查询”，可以理解成返回值为“表”的子查询。又具体可分为：派生表、CTE、内联表值和视图等，在他们的子查询上，有这么一些共同的要求：

• 每一列都要求有列名，包括计算列
• 列名必须唯一
• 不保证排序，所以不能使用ORDER BY。除非配合TOP使用，但即使TOP也不保证排序，TOP的作用是限定行数

派生（Derived） 表

它本质上就是将子查询结果当做表（这个表就被称之为“派生表”）来使用，直接放到外部查询的FROM子句后面，如下所示：

SELECT * FROM( 
	SELECT Id, 
	Id*2 AS DoubleId   -- 这是一个计算列，注意一定要加上一个别名 
	FROM Student) ns   -- 这个ns的别名也是一样必须要有的，即使没有后面的WHERE子句 
WHERE ns.DoubleId > 10 -- WHERE 子句使用子查询表别名ns

注意其语法要求：

• 派生表必须有表别名。
• 构建派生表的子查询必须是独立的，不能是相关的

注意虽然在SQL中出现了两次SELECT，但SQL Server并不需要真的的做两次查询。我们可以查看其执行计划：

一次全表扫描完成，为SQL Server的执行计划点赞！

目前来说，使用派生表最大的用途是：在外部查询中使用派生表上的别名。比如我们之前还没有完全实现的一个需求：

根据Student的Age进行分组，获取每个年龄段的前三位成绩最好的同学

为了获取前三位，我们可能希望这样的SQL语句：

SELECT 
ROW_NUMBER() OVER(PARTITION BY [Age] ORDER BY Score) AS Seq, Id, [Name],Age, Score 
FROM Student 
-- 但以下两种语法都是不被允许的  --WHERE Seq < 3  
--WHERE ROW_NUMBER() OVER(PARTITION BY [UserName] ORDER BY Score) < 3

怎么办呢？只有通过派生表来实现：

SELECT * FROM ( 
    SELECT ROW_NUMBER() OVER(PARTITION BY [Age] ORDER BY Score) AS Seq, 
    Id, [Name], Age, Score FROM Student ) ws -- ws：Windowed Student 
WHERE ws.Seq <= 3 -- 这样就可以使用分区窗口里的Seq了 

派生表被SELECT出来之后，只能被使用一次。如果想要反复使用表子查询的结果，就需要使用：

 

公用表表达式（Common Table Expression）

用CTE改写上面的SQL语句：

WITH ns
AS( 
	SELECT Id, 
	Id*2 AS DoubleId   -- 这是一个计算列，注意一定要加上一个别名 
	FROM Student)    -- 这个ns的别名也是一样必须要有的，即使没有后面的WHERE子句 
SELECT * FROM ns 
WHERE ns.DoubleId > 10 -- WHERE 子句使用子查询表别名ns

注意：上面的SQL语句有一个bug，你能发现并fix么？

我们还可以在CTE名后，显式的指定/改变生成的CET的列名，如下所示：

WITH ns([No], DNo) -- 指定列名No/DNo

还可以一次性定义多个CTE，如下所示：

WITH 
MonthedStudent				-- 第一个CET：MonthedStudent 
AS(SELECT * FROM Student),  -- 从Student表获取数据 
BestStudent					-- 第二个CET：BestStudent，注意不再需要WITH 
AS(SELECT * FROM Exam),		-- 还可以从Exam表中获取数据 
-- 还可以继续创建CET......

和派生表相比，CTE最大的优势是：可以一次定义，多次使用。比如：

WITH MonthedStudent AS( 
	SELECT *, Month(Enroll) AS EnrollMonth FROM Student 
) 
SELECT * FROM MonthedStudent oms				--第一次使用MonthedStudent 
WHERE Score = ( 
	SELECT MAX(Score) FROM MonthedStudent ims	--第二次使用MonthedStudent 
	WHERE oms.[EnrollMonth] = ims.[EnrollMonth] 
)

@想一想@：上述SQL语句想要得到的究竟是什么结果？

但是，CTE在批处理结束之后也会被销毁。如果想更长时间的保留表子查询结果，就需要使用：视图。

在学习视图之前，我们来做一个非常非常常见的需求案例：

分页查询

演示：分页效果（一起帮·文章列表）

方案一：TOP

看到第一页，首先我们想到的可能就是用TOP。我们还是以Student表为例：

SELECT TOP 3 * FROM Student ORDER BY Score -- 分页通常都必然伴随着排序

这样我们能获得第1页的3行数据，但是第2页呢？这时候我们需要“逆向思维”：仍然取TOP 3，但是，是排除第1页数据之后的TOP 3！SQL代码如下：

SELECT TOP 3 * FROM Student 
WHERE Id NOT IN ( 
    SELECT TOP 3 Id FROM Student ORDER BY Score) 
ORDER BY Score 

这是我们在“上古时期”程序员喜欢使用的一种方式，^_^，因为那时候没有太多的选择。

查看执行计划：

这种方案实际上使用了两次全表扫描（Scan）和排序（Sort），当数据量增大的时候，运行速度会变得非常缓慢。

方案二

分页的本质是：查出从第n行开始，其后m行的数据。

@想一想@：如何通过第几页（pageIndex）和每页显示多少篇文章（pageSize）求出上述的n和m呢？

“从第n行开始，其后m行”，同学们有没有想到：BETWEEN...AND？既然数据是有序排列的，我们就可以利用他们的序号，直接使用如下SQL语句啊，是不是？

SELECT * FROM Student WHERE ??? BETWEEN n AND n+m --???代表序号

现在唯一的问题是 序号 从哪里来？

想一想，可不可以使用自增IDENTITY的值？这是不行的！（复习并加深印象：IDENTITY只能保证自增，不能保证连续，完全可能出现：1,2,4,5,7,8,11……这种排列）

所以这时候，我们需要利用T-SQL提供的窗口函数，使用ROW_NUMBER()来生成序列号。使用派生表的SQL代码如下：

SELECT * FROM( 
    SELECT ROW_NUMBER() OVER(ORDER BY Score) AS seq, * 
    FROM Student) s 
WHERE s.seq BETWEEN 4 AND 6

查看其执行计划：

只进行了一次全表扫描，性能上有所提升。

@试一试@：将其转换成利用CTE

方案三：OFFSET和FETCH

因为分页功能使用得如此频繁，所以数据库开发人员一直在对其进行优化，以提供更简洁优美的SQL语法，以及更高的内部性能。

自SQL Server 2012开始，T-SQL可以使用超级简单的OFFSET...FETCH语法：

SELECT * FROM Student ORDER BY Score 
OFFSET 6 ROWS -- 略过6行  
FETCH NEXT 3 ROWS ONLY -- 取其后的3行

查看其执行计划：

超级简洁！但

注意：这是T-SQL语法，不能使用于其他数据库

作业

分别使用派生表和CTE，查询求助表（表中只有一列整体的发布时间，没有年月的列），以获得：

1. 一起帮每月各发布了求助多少篇
2. 每月发布的求助中，悬赏最多的3篇
3. 每个作者，每月发布的，悬赏最多的3篇
4. 分别按发布时间和悬赏数量进行分页查询的结果

临时表

以#开头，存储在tempdb中，会话结束时系统自动清理

• 局部：#table_name，当前会话结束时清除
• 全局：##table_name，所有会话（用户）可见，直到全部用户会话结束时清除
  

表变量

把表当做一个变量存储使用，一样存储在tempdb中

DECLARE @TVariable TABLE(Id INT)
INSERT @TVariable VALUES(986)
SELECT * FROM @TVariable

关于tempdb

除了用户创建的临时表和表变量，SQL Server还利用tempdb完成很多系统工作。

优势：

• 存储：可以在内（缓）存中
• 恢复模式：SIMPLE，即只有最低限度的日志记录
  

但是，占空间，不能被滥用。需要合理的管理使用。

案例：分页查询

分页的本质是查出从第n行开始，其后m行的数据。

思考：如何通过pageIndex和pageSize求出上述的n和m？

能不能用Id？能不能先截断之前的m行，再使用top？

SELECT TOP 3 Id, Reward, Title FROM TProblem
WHERE Id NOT IN (SELECT TOP 3 Id FROM TProblem ORDER BY Reward)
ORDER BY Reward

临时表方案

-- 建一个临时表
CREATE TABLE #rn(
rId INT IDENTITY,
pId INT)

-- 插入数据，生成自增rId
INSERT #rn(pId)
SELECT Id FROM TProblem ORDER BY Reward

-- JOIN获取TProblem完整数据
SELECT TProblem.Id, Title, Author FROM #rn 
JOIN TProblem ON #rn.pId = TProblem.Id
WHERE #rn.rId BETWEEN 4 AND 6

ROW_NUMBER() 表表达式方案

--- 派生表 ---
SELECT * FROM(
SELECT Id, Reward, Title, ROW_NUMBER() OVER(ORDER BY Reward) rn
FROM TProblem) itp
WHERE rn BETWEEN 4 AND 6

----- CTE -----
WITH RN AS 
(SELECT Id, Reward, Title, ROW_NUMBER() OVER(ORDER BY Reward) rn
FROM TProblem)

SELECT * FROM RN WHERE rn BETWEEN 4 AND 6

PS：OFFSET...FETCH（2012之后）

SELECT Id, Reward, Title FROM TProblem ORDER BY Reward 
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY

作业：

尽可能多的写出“查找每个用户关键字最多的求助” 的SQL语句，利用执行计划，找出效率最高的一个。

源栈学历

在当前系列 垃圾桶 中继续学习：

多快好省！前端后端，线上线下，名师精讲

• 先学习，后付费；
• 不满意，不要钱。
• 编程培训班，我就选源栈

更多了解 加：