SQL Server-表表达式基础回顾（二十四）

前言

从这一节开始我们开始进入表表达式章节的学习，Microsoft SQL Server支持4种类型的表表达式：派生表、公用表表达式（CTE）、视图、内嵌表值函数（TVF）。简短的内容，深入的理解，Always to review the basics。

表表达式

表表达式没有任何的物理实例化，在查询表表达式时它们是虚拟的，内部查询是非嵌套的，换句话说，外部查询和内部查询直接合并到一个底层对象的查询中，使用表表达式的好处通常与代码的逻辑方面有关，而与代码的性能无关-摘抄自SQL Server 2012基础教程。在使用表表达式时我们必须满足以下3点要求，否则将会报错。我们下面来简短介绍下表表达式的4中类型。

（1）无法保证顺序。

（2）所有列都必须具有名称。

（3）所有列名都必须是唯一的。

派生表

派生表（也称为子查询表）是在外部查询的FROM子句中定义的，它们存在的范围是外部查询。一旦外部查询完成后，派生表就消失了。我们看一个简单的派生表的例子。

USE TSQL2012
GO

SELECT *
FROM(
    SELECT * FROM Sales.Customers WHERE country = N'USA') AS USACusts;

我们再来具体看下上述已经明确说过表表达式查询满足的条件，接下来我们进行如下查询：

USE TSQL2012
GO

SELECT *
FROM(
    SELECT * FROM Sales.Customers WHERE country = N'USA' ORDER BY custid) AS USACusts;

当我们在子查询中添加ORDER BY之后就出现如上错误，这也就是说的上述表表达式要求的第一点，表表达式作为关系表，因为关系在源于集合理论，所以无法保证输出数据的顺序，看到SQL Server 2012基础教程中是这么说，我也就这么理解，至于真正原因还是无法理解，反正在表表达式中千万不要进行ORDER BY。关于要求的第二点和第三点就不用多说，比如上述此时对表不起别名肯定会报错，还有当对多个表进行联接时，表中列字段肯定有一样的，为保证唯一，我们必须为列名起别名来解决不唯一的问题。使用表表达式的好处之一就是在外部查询的任何子句中，可以引用内部查询的SELECT子句中分配的列别名，如此这样可以帮助我们绕开在SELECT子句逻辑处理之前的查询子句中（如WHERE、GROUP BY）无法引用SELECT子句中分配的列别名的实际问题，到底是什么意思呢，我们知道进行常规的查询时，此时如WHERE、GROUP BY是在SELECT之前进行，所以会导致我们对SELECT中的列通过WHERE、GROUP BY无法进行引用，我们来看一下以下例子。

USE TSQL2012
GO

SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS custids
FROM Sales.Orders
GROUP BY orderyear

如上此时我们对SELECT中的orderyear通过GROUP BY来进行分组，但是GROUP BY操作是在SELECT之前所以会导致出现如下错误。

要解决这个问题我们可以通过表表达式中的派生表来查询

USE TSQL2012
GO

SELECT orderyear, COUNT(DISTINCT custid) AS custids
FROM (SELECT  YEAR(orderdate) AS orderyear, custid FROM Sales.Orders) AS  SO
GROUP BY orderyear

对于派生表可以引用参数来用于存储过程或函数等变量或输入参数，同时派生表可以进行嵌套，如下：

USE TSQL2012
GO


SELECT orderyear, numcusts
FROM (
    SELECT orderyear, COUNT(DISTINCT custid) AS numcusts 
    FROM (
        SELECT YEAR(orderdate) AS orderyear, custid 
        FROM Sales.Orders) AS D1 
        GROUP BY orderyear)AS D2
WHERE numcusts > 70;

当有多个表时这样进行嵌套时此时代码会越来越复杂，冗长的代码不利于维护容易导致出错，同时也降低了代码的可读性。此时我们可以用表表达式的第2种形式CTE。

公用表表达式（CTE）

CTE通过WITH语句定义，具有如下常用形式。

WITH <CTE_NAME>[(<target_column_list>)]
AS
(
       <inner_query_defining_CTE>       
)
<outer_query_against_CTE>

我们来看一个关于CTE简单的例子

USE TSQL2012
GO

WITH USACusts AS
(
    SELECT custid, companyname
    FROM Sales.Customers
    WHERE country = N'USA'
)
SELECT * FROM USACusts

和派生表相同，一旦外部查询完成后，CTE马上就会消失。在CTE中我们同样可以使用参数，如下：

USE TSQL2012
GO

DECLARE @empid AS INT = 3;

WITH C AS
(
    SELECT YEAR(orderdate) AS orderyear, custid
    FROM Sales.Orders
    WHERE empid = @empid
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear

我们同样可以类似实现派生表一样的嵌套，如下：

USE TSQL2012
GO


WITH C1 AS
(
    SELECT YEAR(orderdate) AS orderyear, custid
    FROM Sales.Orders
),C2 AS
(
    SELECT orderyear,COUNT(DISTINCT custid) AS numcusts
    FROM C1
    GROUP BY orderyear
)

SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70

这里我们利用CTE实现了和派生表同样的结果，派生表和CTE其实只是在语义上有差异，但是相对于派生表最主要的优势在于不需要像派生表那样需要多重嵌套，而CTE只要定义了就无需嵌套，每个CTE在代码中以模块化的方式分别出现。这中模块化的方式和嵌套派生表方式相比，大大提高了代码的可读性和可维护性，若有多个表需要嵌套利用CTE来实现更加清爽并有助于代码的清晰性。而对于派生表的另外一个优势在于就外部查询的FROM子句而言，CTE在之前就已经存在，因此可以引用同一个CTE的多个实例。

视图（VIEW）

视图和内嵌表值函数是两种可以重复使用的表表达式类型，其定义被存储为数据库对象，创建之后，这些对象是数据库的永久部分，并且只有在显式删除它们时才能从数据库中删除。我们看下如何创建视图并使用视图。

USE TSQL2012
GO

IF OBJECT_ID('Sales.USACusts') IS NOT NULL
    DROP VIEW Sales.USACusts;
GO

CREATE VIEW Sales.USACusts
AS

SELECT custid, companyname, contactname, contacttitle, [address]
FROM Sales.Customers
WHERE country = N'USA'

GO

创建视图完之后视图对象就在数据库中已经存在，此时我们再来查询视图

USE TSQL2012
GO

SELECT * 
FROM Sales.USACusts

内嵌表值函数（TVF）

内嵌表值函数是支持输入参数的可重复使用的表表达式。除了支持输入参数之外的其他所有方面都和视图类似。我们来看下怎么创建内嵌表值函数。

USE TSQL2012
GO


IF OBJECT_ID('dbo.GetCustOrders') IS NOT NULL
    DROP FUNCTION dbo.GetCustOrders;
GO

CREATE FUNCTION dbo.GetCustOrders(@cid AS INT) RETURNS TABLE
AS RETURN
    SELECT orderid, custid, empid, orderdate, requireddate, shippeddate, shipperid, shipcity,
            shipaddress, shipregion, freight
    FROM Sales.Orders
    WHERE custid = @cid
GO

此时我们创建完毕TVF，我们接下来来调用自定义的TVF

USE TSQL2012
GO


SELECT orderid, custid
FROM dbo.GetCustOrders(1) AS O;

上述我们为表表达式提供了一个别名，虽然不是必须的，但是推荐这样做，因为它使代码更具有可读性和少出错误。本节我们对表表达式的4种方式作了一下回顾，同样我们来为这4种形式的表表达式来做个结论。

（1）表表达式可以简化代码，提高代码的可维护性和封装查询逻辑。

（2）当需要使用表表达式并且不打算重复使用其定义时，可以使用派生表或CTE，而CTE对派生表具有更多优势不需要像派生表那样嵌套CTE，使用CTE使代码更加模块化和便于维护，此外，还可以引用同一个CTE的多个实例，这一点是派生表无法实现的。

（3）当需要使用表表达式并且需要定义可重复使用的表表达式时，可以使用视图或内嵌表值函数，当不需要支持输入参数时，可以使用视图，否则，应当使用内嵌表值函数（TVF）。

总结

本节回顾了表表达式的基础内容，下节我们详细讲讲使用视图的限制，简短的内容，深入的理解，我们下节再会。