《SQL Server 2012 T-SQL基础》读书笔记

Chapter 5 Table Expressions

一个表表达式（table expression）是一个命名的查询表达式，代表一个有效的关系表。SQL Server包括4种表表达式：派生表（derived tables）、公用表表达式（common table expressions (CTEs),）、视图（views）、内联表值函数（inline table-valued functions (inline TVFs)）。使用表表达式的好处通常在于逻辑层面而非性能。

派生表（derived tables）

举个例子先：

SELECT *

FROM (SELECT custid, companyname

      FROM Sales.Customers

      WHERE country = N'USA') AS USACusts;

在这里你用了一个查询来定义了一个叫USACusts的派生表，它只存活于外部查询中，也就是当外部查询结束了，它也没了。

想用一个查询定义一个表表达式，这个查询必须满足以下几点：

1.Order is not guaranteed（不保证查出来的结果集有顺序，也就是说不能用ORDER BY，除非是用TOP或者OFFSET的时候）.

2.All columns must have names.

3.All column names must be unique.这种情况通常发生在联接两个表的时候，如果两个表有相同名字的列，那么在SELECT中要分别给他们定义别名。

其实以上几点都基于这么一个事实：一个表表达式代表了一个关系。

本来需要写两遍某个表达式的SQL：

SELECT

  YEAR(orderdate) AS orderyear,

  COUNT(DISTINCT custid) AS numcusts

FROM Sales.Orders

GROUP BY YEAR(orderdate);

现在可以只写一遍：

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts

FROM (SELECT YEAR(orderdate) AS orderyear, custid

      FROM Sales.Orders) AS D

GROUP BY orderyear;

之前说过，表表达式只是逻辑层面，SQL Server在执行时还是会把表表达式扩展为第一个要写两遍的那种形式。

关于用AS定义列名，还有另一种语法：

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts

FROM (SELECT YEAR(orderdate), custid

      FROM Sales.Orders) AS D(orderyear, custid)

GROUP BY orderyear;

但作者不推荐这种写法，因为不是很清晰。但是如果不再修改表表达式，只是拿它当一个黑箱使用，那么也许你可以这么写（因为你只关心列名是什么）。

当然，也可以嵌套多层派生表，如：

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;

但作者不建议这样，因为他说这样会problematic，还不如“写两遍”的写法：

SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts

FROM Sales.Orders

GROUP BY YEAR(orderdate)

HAVING COUNT(DISTINCT custid) > 70;

如果你想在FROM子句中定义多个派生表，然后把他们Join一下，是可以的，但是你不能：比如先定义好一个派生表叫A，然后再直接LEFT OUTER JOIN A（也就是不能refer to它的多个实例，只能一模一样的再重新写一遍）。

Common table expressions (CTEs)

要定义一个CTE：

WITH <CTE_Name>[(<target_column_list>)]

AS

(

  <inner_query_defining_CTE>

)

<outer_query_against_CTE>;

举个例子：

WITH USACusts AS

(

  SELECT custid, companyname

  FROM Sales.Customers

  WHERE country = N'USA'

)

SELECT * FROM USACusts;

与派生表一样，当outer query完成时，CTE结束生命。

用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;

也就是说后定义的CTE可以引用之前定义的CTE。

同样地，CTE可以解决之前“在FROM中无法引用同一个派生表的多个实例”的问题。但最终SQL Server还是会多次扫描同一个表，如果你介意性能的话，可以把结果存到一个临时表或者表变量。

定义一个Recursive CTEs（递归CTE）如下：

WITH <CTE_Name>[(<target_column_list>)]

AS

(

  <anchor_member>

  UNION ALL

  <recursive_member>

)

<outer_query_against_CTE>;

与正常CTE定义的唯一区别只在于AS括号里面的查询。<anchor_member>是第一次调用返回的结果，<recursive_member>是之后每一次调用的结果，直到为空。举个例子，如果你想得到一个员工的所有下属（直接或非直接），那么就：

WITH EmpsCTE AS

(

  SELECT empid, mgrid, firstname, lastname

  FROM HR.Employees

  WHERE empid = 2

  UNION ALL

  SELECT C.empid, C.mgrid, C.firstname, C.lastname

  FROM EmpsCTE AS P

    JOIN HR.Employees AS C

      ON C.mgrid = P.empid

)

SELECT empid, mgrid, firstname, lastname

FROM EmpsCTE;

得到结果：

注意上面结果中mgrid是指这个人的manager的empid是多少。第一次调用就是返回第一行，这个结果作为第二次调用里面的EmpsCTE这个玩意儿，于是第二次调用返回第2，第3行，再第三次调用用第二次调用的结果集....以此类推。

Views（视图）

与上面的两种表表达式不同，Views和inline TVFs都会被存储为database objects，所以他们的生命周期更长。定义一个叫USACusts的视图：

CREATE VIEW Sales.USACusts

AS

  SELECT

  custid, companyname, contactname, contacttitle, address

  FROM Sales.Customers

  WHERE country = N'USA';

定义View的时候不要用SELECT *，因为如果之后又加了一个列到你SELECT FROM的表中的话，由于你的视图的metadata还是不变，所以你查询这个视图还是只能得到原来的列（可以用sp_refreshview或sp_refreshsqlmodule来刷新view的metadata）。

创建View的时候可以指定一些选项（跟在WITH 后面）：

ENCRYPTION选项就是告诉SQL Server在存储这个东西的定义的时候，进行obfuscate（混淆）处理，可以用在create or alter views, stored procedures, triggers, and user-defined functions (UDFs)的时候，比如：

ALTER VIEW Sales.USACusts WITH ENCRYPTION AS...

然后如果你再：SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));就会得到NULL。

SCHEMABINDING，当你指定了这个选项的话，有点像外键约束，就比如定义视图的查询是SELECT address FROM Sales.Customers，那么如果你想删掉这一行的话：

ALTER TABLE Sales.Customers DROP COLUMN address;你会得到错误。这个选项是个good practice。

CHECK OPTION，举个例子吧：默认情况下，你可以INSERT INTO Sales.USACusts这个视图一些country不是N'USA'的rows, 因为最终其实还是INSERT到实际的table中去，如果你不想让这样的INSERT（或类似的UPDATE）发生，就指定这个选项。

Inline Table-Valued Functions可以理解为有输入参数的视图，例如：

CREATE FUNCTION dbo.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;

然后要用的话就：

SELECT orderid, custid

FROM dbo.GetCustOrders(1) AS O;

CROSS APPLY这个运算符有点像CROSS JOIN，它接受两个表，右边的表可以是一个表表达式，对于左边的表的每一行，都对应一个右边的表，然后合并起来，举个例子：

SELECT C.custid, A.orderid, A.orderdate

FROM Sales.Customers AS C

  CROSS APPLY

    (SELECT TOP (3) orderid, empid, orderdate, requireddate

     FROM Sales.Orders AS O

     WHERE O.custid = C.custid

     ORDER BY orderdate DESC, orderid DESC) AS A;

这个的意思就是：对于每个Customer（左边的表），返回他最新的三个订单（右边的表），查询结果为：

可以注意到，每个custid都有三行。如果是子查询的话，每个custid就只有一行了，你只能把每个Customer的最新的三个订单合并成一个标量（比如用 FOR XML PATH）。

如果对于左边表里面的某个列，右边的表是个空集，那么左边这一行就不会在整个的查询结果中，如果你想包含它们的话，就用OUTER APPLY，如果把上面的CROSS APPLY改成OUTER APPLY，那么查询结果为：

如果改用incline TVS的话会增加可读性和可维护性：

CREATE FUNCTION dbo.TopOrders

  (@custid AS INT, @n AS INT)

  RETURNS TABLE

AS

RETURN

  SELECT TOP (@n) orderid, empid, orderdate, requireddate

  FROM Sales.Orders

  WHERE custid = @custid

  ORDER BY orderdate DESC, orderid DESC;

然后就可以用了：

SELECT

  C.custid, C.companyname,

  A.orderid, A.empid, A.orderdate, A.requireddate

FROM Sales.Customers AS C

  CROSS APPLY dbo.TopOrders(C.custid, 3) AS A;

巴特西

《SQL Server 2012 T-SQL基础》读书笔记 - 5.表表达式

Chapter 5 Table Expressions

派生表（derived tables）

Common table expressions (CTEs)

Views（视图）

最新文章

热门文章