笔记-Microsoft SQL Server 2008技术内幕：T-SQL语言基础-05 表表达式

公用表表达式

公用表表达式（CTE，Common table expression）是用WITH子句定义的，一般格式为：

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

 和派生表一样，一旦外部查询完成，CTE的生命期就结束了。

CTE分配列别名的方式——内联格式和外部格式，内联格式：

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

 外部格式：

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

 定义多个CTE：

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;

 与嵌套的派生表代码相比，上面这种模块化的代码大大提高了代码的可读性和可维护性。

视图

创建一个视图：

USE TSQLFundamentals2008;
IF OBJECT_ID(‘Sales.USACusts‘) IS NOT NULL
  DROP VIEW Sales.USACusts;
GO
CREATE VIEW Sales.USACusts
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N‘USA‘;
GO

 记住一点，在定义表表达式的查询语句中不允许出现ORDER BY子句，因为关系表之间的行没有顺序。试图创建一个有序视图也是不合理的，SQL Server将会报错。应该在使用视图的外部查询中使用ORDER BY子句。

内联表值函数

以下代码创建一个内联表值函数：

USE TSQLFundamentals2008;
IF OBJECT_ID(‘dbo.fn_GetCustOrders‘) IS NOT NULL
  DROP FUNCTION dbo.fn_GetCustOrders;
GO
CREATE FUNCTION dbo.fn_GetCustOrders
  (@cid AS INT) RETURNS TABLE
AS
RETURN
  SELECT orderid, custid, empid, orderdate, requireddate,
    shippeddate, shipperid, freight, shipname, shipaddress, shipcity,
    shipregion, shippostalcode, shipcountry
  FROM Sales.Orders
  WHERE custid = @cid;
GO

 使用这个函数：

SELECT orderid, custid
FROM dbo.fn_GetCustOrders(1) AS CO;

总结

借助表表达式可以简化代码，提高代码的维护性，还可以封装查询逻辑。当需要使用表表达式时，如果是不计划重用它们的定义，则使用派生表或者CTE；当需要定义可重用的表表达式时，可以使用视图和内联表值函数。

笔记-Microsoft SQL Server 2008技术内幕：T-SQL语言基础-05 表表达式

标签：