重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
SQLServer-表表达式基础-创新互联
表表达式
表表达式没有任何的物理实例化，在查询表表达式时它们是虚拟的，内部查询是非嵌套的，换句话说，外部查询和内部查询直接合并到一个底层对象的查询中，使用表表达式的好处通常与代码的逻辑方面有关，而与代码的性能无关-摘抄自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[( )] AS ( )
我们来看一个关于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）。
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
本文题目：SQLServer-表表达式基础-创新互联
分享网址：http://cqcxhl.com/article/djpggo.html