第五章 表表达式 T-SQL语言基础

表表达式

Microsoft SQL Server支持4种类型的表表达式:派生表(derived table),公用表表达式(CTE,common table expression),视图,以及内联表值函数(inline TVF,inline table-valued function).

(重点介绍表表达式在SELECT查询中的用法).

表表达式并不是物理上真实存在的什么对象,它们是虚拟的.使用表表达式的好处通常体现在代码的逻辑方面,而不是性能方面.

(联接表表达式的APPLY表运算符;如何用APPLY这个运算符为另一个表的每一行来应用某个表表达式)

要有效地定义任何类型的表表达式,查询语句必须满足三个要求:

1.不保证有一定的顺序.

表表达式代表的是一个表(无序的).ANSI SQL不允许在用于定义表表达式的查询语句中有ORDER BY子句.(TOP除外!在带有TOP选项的查询语句中,ORDER BY子句的逻辑目的只有一个:为TOP选项定义要筛选出哪些行.如果用带有TOP和ORDER BY子句的查询语句来定义表表达式,ORDER BY保证只为TOP选项提供逻辑筛选数据的服务,而不用于通常数据展示的目的.)

2.所有的列必须有名称.

表中的所有列必须有列名,在用于定义表表达式的查询语句中,必须为SELECT列表中的表达式起别名.

3.所有的列名必须是唯一的.

表中所有的列名必须是唯一的.如果表表达式中有多个列具有相同的名称,则该表表达式是无效的.(如果确实需要在表表达式中包括这两个同名列,它们就必须具有不同的列名,可以为这两个列起不同的列别名)

5.1 派生表

派生表(也称为表子查询)是在外部查询的FROM子句中定义的.派生表的存在范围为定义它的外部查询,只要外部查询一结束,派生表也就不存在了.

派生表的定义

---------------------------------------------------------------------
-- Derived Tables
---------------------------------------------------------------------

--定义派生表的查询语句要写在一对圆括号内,后面跟着AS子句和派生表的名称...
USE TSQLFundamentals2008;

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

5.1.1 分配列别名

使用表表达式的一个好处是:在外部查询的任何子句中都可以引用在内部查询的SELECT子句中分配的列别名.

一个限制:不能在逻辑处理顺序先于SELECT子句的其他查询子句(如WHERE或GROUP BY)中对SELECT子句分配的列别名进行引用!

---------------------------------------------------------------------
-- Assigning Column Aliases
---------------------------------------------------------------------

-- Following fails
--一个限制:不能在逻辑处理顺序先于SELECT子句的其他查询子句(如WHERE或GROUP BY)中对SELECT子句分配的列别名进行引用!
SELECT
  YEAR(orderdate) AS orderyear,
  COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY orderyear;
GO

-- Listing 5-1 Query with a Derived Table using Inline Aliasing Form
-- 查询对象是一个名为D的表,这个表有两列:orderyear和custid,所以之后的都可以调用orderyear,custid
-- 注:强调使用表表达式是出于逻辑原因,而与性能无法.一般来说,表表达式既不会对性能产生正面影响,也不会对性能产生负面影响.
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate) AS orderyear, custid
      FROM Sales.Orders) AS D
GROUP BY orderyear;

-- 可以在GROUP BY和SELECT子句中都引用 YEAR(orderdate) 这个表达式...
SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY YEAR(orderdate);

-- External column aliasing
-- 在表表达式的名称后面,在一对圆括号中一次性指定所有目标列的名称...
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate), custid
      FROM Sales.Orders) AS D(orderyear, custid)
GROUP BY orderyear;
GO

--内联别名格式:(SELECT YEAR(orderdate) AS orderyear, custid FROM Sales.Orders) AS D
--外部命名格式:(SELECT YEAR(orderdate), custid FROM Sales.Orders) AS D(orderyear, custid)

--通常建议使用内联别名格式...
--原因:1.使用内联格式时,如果须要调试代码,只要把定义表表达式的查询语句独立出来,再运行,在结果中出现的列名就是原来指定的列名.
--     2.使用外部格式,当把表表达式查询独立出来后,就不能在其中包含目标列名,这时如果查询中有未命名的表达式,在结果中这些表达式对应的列就没有列名.
--     当表表达式的查询很长,采用外部命名格式可能很难分辨出列别名所属的表达式.
--即使使用内联别名格式是最佳实践方法,在某些情况下,外部命名格式可能用起来更方便.

5.1.2 使用参数

在定义派生表的查询中,可以引用参数.参数可以是局部变量和例程(如存储过程或函数)的输入参数.

---------------------------------------------------------------------
-- Using Arguments
---------------------------------------------------------------------

--以下这个查询返回指定雇员每年处理的订单所属的不同客户数量,输入的雇员ID保存在变量@empid之中.
-- Yearly Count of Customers handled by Employee 3
DECLARE @empid AS INT = 3;

/*
-- Prior to SQL Server 2008 use separate DECLARE and SET statements:
DECLARE @empid AS INT;
SET @empid = 3;
*/

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

5.1.3 嵌套

如果须要用一个本身就引用了某个派生表的查询去定义另一个派生表,最终得到的就是嵌套派生表.

---------------------------------------------------------------------
-- Nesting
---------------------------------------------------------------------

-- Listing 5-2 Query with Nested Derived Tables
--以下两个查询:都是返回订单年份和该年处理的客户数,但要求每个订单年份处理的客户数要多于70人.
--一个是嵌套派生表,一个是分组查询
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;

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

5.1.4 派生表的多引用

派生表另一个存在问题的方面源于派生表是在外部查询的FROM子句中定义的,其逻辑处理顺序并不优先于外部查询.当对外部查询的FROM子句进行处理时,派生表其实并不存在.因此,如果须要引用派生表的多个实例,这时还不能这样做.相反,必须基于同一查询去定义多个派生表.

---------------------------------------------------------------------
-- Multiple References
---------------------------------------------------------------------

-- Listing 5-3 Multiple Derived Tables Based on the Same Query
--以下查询:第一个派生表(Cur)代表当前年份,另一个派生表(Prv)代表上一年份.联接条件:Cur.orderyear = Prv.orderyear+1,用于说明第一个派生表的每一行怎么和第二个派生表的上一年进行匹配.通过采用左联接,第一年虽然没有上一年,但也会从Cur表中返回.外部查询的SELECT子句计算了当前年份和上一年份处理过的客户数量之差.
SELECT Cur.orderyear, 
  Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts,
  Cur.numcusts - Prv.numcusts AS growth
FROM (SELECT YEAR(orderdate) AS orderyear,
        COUNT(DISTINCT custid) AS numcusts
      FROM Sales.Orders
      GROUP BY YEAR(orderdate)) AS Cur
  LEFT OUTER JOIN
     (SELECT YEAR(orderdate) AS orderyear,
        COUNT(DISTINCT custid) AS numcusts
      FROM Sales.Orders
      GROUP BY YEAR(orderdate)) AS Prv
    ON Cur.orderyear = Prv.orderyear + 1;

5.2 公用表表达式(CTE)

公用表表达式(CTE,Common table expression)是和派生表很相似的另一种形式的表表达式,而且具有一些重要优势...

---------------------------------------------------------------------
-- Common Table Expressions
---------------------------------------------------------------------

--CTE是用WITH子句定义的
WITH USACusts AS
(
  SELECT custid, companyname
  FROM Sales.Customers
  WHERE country = N'USA'
)
SELECT * FROM USACusts; --外部查询 ! 和派生表一样,一旦外部查询完成,CTE的生命期也就结束了.

5.2.1 分配列别名

CTE也支持两种格式的列别名命名方式--内联格式和外部格式.

内联格式: 指定<expression> as <column_alias>

外部格式: 在CTE名称后面的一对圆括号中指定目标列的列表.

---------------------------------------------------------------------
-- Assigning Column Aliases
---------------------------------------------------------------------

-- Inline column aliasing
-- 内联格式:指定<expression> as <column_alias>
WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;

-- External column aliasing
-- 外部格式:在CTE名称后面的一对圆括号指定目标列的列表
WITH C(orderyear, custid) AS
(
  SELECT YEAR(orderdate), custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
GO

 5.2.2 使用参数

---------------------------------------------------------------------
-- Using Arguments
---------------------------------------------------------------------

DECLARE @empid AS INT = 3;

/*
-- Prior to SQL Server 2008 use separate DECLARE and SET statements:
DECLARE @empid AS INT;
SET @empid = 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;
GO

5.2.3 定义多个CTE

如果须要在一个CTE中引用另一个CTE,不须要像派生表那样进行嵌套,相反,只要简单地在同一WITH子句中定义多个CTE,并用逗号把它们分隔开.每个CTE可以引用在它前面定义的所有CTE,而外部查询则可以引用所有CTE.

---------------------------------------------------------------------
-- Defining Multiple CTEs
---------------------------------------------------------------------

--CTE引用另一回CTE,不须要像派生表那样进行嵌套;只需在同一WITH子句中定义多个CTE,并用逗号把它们分隔开.
--每个CTE可以引用在它前面定义的所有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;

5.2.4 CTE的多引用

CTE是先定义,再查询,就外部查询的FROM子句来说,CTE是已经存在的.因此,可以引用同一个CTE的多个实例.

---------------------------------------------------------------------
-- Multiple References
---------------------------------------------------------------------

--CTE是先定义,再查询,就外部查询的FROM子句来说,CTE是已经存在的.因此,可以引用同一个CTE的多个实例.
WITH YearlyCount AS
(
  SELECT YEAR(orderdate) AS orderyear,
    COUNT(DISTINCT custid) AS numcusts
  FROM Sales.Orders
  GROUP BY YEAR(orderdate)
)
SELECT Cur.orderyear, 
  Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts,
  Cur.numcusts - Prv.numcusts AS growth
FROM YearlyCount AS Cur
  LEFT OUTER JOIN YearlyCount AS Prv
    ON Cur.orderyear = Prv.orderyear + 1;

--表表达式通常对性能没有任何影响,因为它们没有任何物化的存在.SQL Server会对这个例子中的两处CTE引用进行扩展,在数据库引擎内部,这个查询有一个联接Orders表的两个实例的自联接,在联接之前每个实例都要扫描和聚合表数据.采用派生表的方法也须要经历同样的物理处理步骤.

5.2.5 递归CTE

---------------------------------------------------------------------
-- Recursive CTEs (Advanced, Optional)
---------------------------------------------------------------------
--*****递归CTE
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;

5.3 视图

视图的创建

---------------------------------------------------------------------
-- Views Described
---------------------------------------------------------------------

-- 以下为视图的创建!
-- Creating VUSACusts View
-- 注:一般推荐在和视图有关的上下文中应该避免使用 SELECT * 语句.列是在编译视图时进行枚举的,新加的列可能不会自动加到视图中.
--   最好的开发实践就是在视图的定义中显示地列出需要的列名.如果在底层中添加了列,而且在视图中需要这些新加的列,则可以使用ALTER VIEW 语句对视图定义进行相应的修改.
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


--查询语句!
SELECT custid, companyname
FROM Sales.USACusts;
GO

111