基础很重要~~04.表表达式-上篇

本系列【T-SQL基础】主要是针对T-SQL基础的总结。

【T-SQL基础】01.单表查询-几道sql查询题

【T-SQL基础】02.联接查询

【T-SQL基础】03.子查询

【T-SQL基础】04.表表达式-上篇

【T-SQL基础】04.表表达式-下篇

【T-SQL基础】05.集合运算

【T-SQL基础】06.透视、逆透视、分组集

【T-SQL基础】07.数据修改

【T-SQL基础】08.事务和并发

【T-SQL基础】09.可编程对象

 ----------------------------------------------------------

【T-SQL进阶】01.好用的SQL TVP~~独家赠送[增-删-改-查]的例子

 ----------------------------------------------------------

【T-SQL性能调优】01.TempDB的使用和性能问题

【T-SQL性能调优】02.Transaction Log的使用和性能问题

【T-SQL性能调优】03.执行计划

【T-SQL性能调优】04.死锁分析

持续更新......欢迎关注我！

概述：

本篇主要是对表表达式中派生表和公用表表达式基础的总结。

表表达式包含四种：

　　1.派生表

　　2.公用表表达式

　　3.视图

　　4.内联表值函数

本篇是表表达式的上篇，只会讲到派生表和公用表表达式，下篇会讲到视图和内联表值函数。

下面是表表达式的思维导图：

 

 

表表达式：

1.一种命名的查询表达式，代表一个有效的关系表。

2.可以像其他表一样，在数据处理语句中使用表表达式。

3.在物理上不是真实存在的什么对象，它们是虚拟的。对于表达式的查询在数据库引擎内部都将转化为对底层对象的查询。

为什么使用表表达式：

1.使用表表达式的好处是逻辑方面，在性能上没有提升。

2.通过模块化的方法简化问题的解决方案，规避语言上的某些限制。在外部查询的任何字句中都可以引用在内部查询的SELECT字句中分配的列别名。比如在SELECT字句中起的别名，不能在WHERE,group by等字句（逻辑顺序位于SELECT字句之前的字句）中使用，通过表表达式可以解决这类问题。

一、派生表

1.用法：

使用地方：外部查询的FROM字句中定义

存在范围：外部查询一结束，派生表就不存在

语法：派生表的查询语句写在括号里面，括号外面跟着AS关键字和派生表的名称。

SELECT columns FROM ( SELECT columns FROM Table ) AS TABLE_1

例子：

1 2 3 4 5

SELECT   * FROM     (  SELECT     orderdate            FROM       Sales.Orders            WHERE      orderdate =  '20070101'          )  AS  O1

 

定义了一个O1的派生表，返回所有订单日期='20070101'的订单，外部查询返回派生表的所有行。

在这个例子中，其实不需要派生表，只是为了演示派生表的语法。

注意：

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

（1）不保证有一定的顺序。

表表达式是一个表，而关系表中的行驶没有固定顺序的，所以 ANSI SQL 不允许表表达式查询语句中出现ORDER BY字句。除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

（2）所有的列必须有名称

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

当表表达式的查询联接了两个表，而这两个表存在名称相同的列时，就会出现相同名称的列，如果要使表达式有效，则必须使这两个表的列名不一样。

2.分配列别名

例子：

下面的SQL语句是无效的：

1 2 3

SELECT   YEAR (orderdate)  AS  orderyear FROM     Sales.Orders GROUP  BY  orderyear

方案一：SELECT字句和GROUP BY字句中都定义YEAR(orderdate)

1 2 3

SELECT   YEAR (orderdate)  AS  orderyear FROM     Sales.Orders GROUP  BY  YEAR (orderdate)

方案二：派生表

因为GROUP BY语句的是在SELECT字句之前执行的，所以GROUP BY字句不能引用SELECT字句中定义的别名orderyear。

解决办法是使用表表达式：

1 2 3 4 5 6

SELECT   orderyear FROM     (  SELECT     YEAR (orderdate)  AS  orderyear            FROM       Sales.Orders          )  AS  O1   GROUP  BY  orderyear

结果：

派生表O1是字段orderyear的集合，外部查询SELECT字句和GROUP BY字句可以引用派生表O1的orderyear字段。

SQL SERVER在执行时会扩展表表达式的定义，以便直接访问底层对象。扩展后，与方案一类似。

一般来说，表表达式既不会对性能产生正面影响，也不会对性能产生负面影响。

3.使用参数

在派生表的查询中，可以引用参数。

例子：

基于上面的例子，我们定义了一个标量@orderid，在派生表查询语句中的WHERE字句中引用这个参数。

1 2 3 4 5 6 7 8

--定义参数@orderid的值等于，参数类型为INT类型 DECLARE  @orderid  AS  INT  = 10248           SELECT   orderyear FROM     (  SELECT             FROM       Sales.Orders            WHERE  orderid = @orderid          )  AS  O1 GROUP  BY  orderyear

4.嵌套

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

例子：查询用于返回订单年份和该年处理的客户数，要求每个订单年份处理的客户数要多于10人

方案一：我们用第一节中单表查询查询出结果

 

1 2 3 4 5 6

SELECT   YEAR (orderdate)  AS  orderyear ,          COUNT ( DISTINCT  custid)  AS  numcuts FROM     Sales.Orders GROUP  BY  YEAR (orderdate)   HAVING   COUNT ( DISTINCT  custid) > 10

方案二：嵌套派生表

1 2 3 4 5 6 7 8 9 10 11

SELECT   orderyear ,          numcuts FROM     (  SELECT     orderyear ,                      COUNT ( DISTINCT  custid)  AS  numcuts            FROM       (  SELECT     YEAR (orderdate)  AS  orderyear ,                                  custid                        FROM       Sales.Orders                      )  AS  O1            GROUP  BY   orderyear          )  AS  O2 WHERE    numcuts > 10

嵌套查询看起来非常复杂，嵌套查询也是很容易产生问题的一个方面。在这个例子中，使用嵌套派生表的目的是为了重用列别名。但是，由于嵌套增加了代码的复杂性，所以对于本例考虑使用方案一。

5.练习题

1.写一个查询，为每个雇员返回其订单日期最近的所有订单（用派生表）

期望结果：

本题分两个步骤：

1.可以先查询出每个雇员的订单的最大日期，生成派生表O1

2.O1与Sales.Orders表进行关联，查询出每个雇员处理过的订单日期等于最大的订单日期的所有订单。

1 2 3 4 5 6 7 8 9 10 11

SELECT   O2.empid ,          O2.orderdate ,          orderid ,          custid FROM     (  SELECT     empid ,                      MAX (orderdate)  AS  orderdate            FROM       Sales.Orders            GROUP  BY   empid          )  AS  O1          INNER  JOIN  Sales.Orders  AS  O2  ON  O1.empid = O2.empid                                           AND  O1.orderdate = O2.orderdate

二、公用表表达式

公用表表达式是和派生表相似的另一种形式的表表达式，但是公用表表达式具有一些优势。

1.语法：

内联格式：别名写在内部查询中

1 2 3 4 5 6 7 8 9 10 11 12 13 14

WITH  表名 AS (      内部查询 ) 外部查询<br> 例子： WITH  O1 AS (      SELECT  custid,companyname  FROM  Sales.Customers )   SELECT  *  FROM  O1

外联格式：列的别名写在外部查询中

1 2 3 4 5 6 7 8 9 10 11 12 13 14

WITH  表名(别名) AS (      内部查询 ) 外部查询   例子： WITH  O1(custid_1,companyname_1) AS (      SELECT  custid,companyname  FROM  Sales.Customers ) SELECT  *  FROM  O1

2.用法

公用表表达式和派生表一样，前面需要遵守的规则对公用表表达式同样适用。当外部查询结束，公用表表达式的生命周期就结束了。

3.使用参数

和派生表一样，可以引用参数。

例子：

1 2 3 4 5 6 7 8 9

DECLARE  @custid  AS  INT  = 10248;    WITH  O1(custid_1,companyname_1) AS (      SELECT  custid,companyname  FROM  Sales.Customers      WHERE  @custid = @custid ) SELECT  *  FROM  O1

4.定义多个CTE

CTE和派生表相比具有以下优势：

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

如下面的例子，将嵌套派生表改为多个CTE:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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

5.练习题

为每个订单按照orderdate、orderid的顺序来计算其行号，并返回行号在11~20之间的行。

期望结果：

本题分两个步骤：

1.查询出基于orderdate，orderid排序的结果集，然后将这个查询语句用CTE来表示

2.查询第一步结果集中11~20之间的行

1 2 3 4 5 6 7 8 9 10 11 12 13 14

WITH  O1 AS (      SELECT   orderid ,              orderdate ,              custid ,              empid ,              ROW_NUMBER() OVER (  ORDER  BY  orderdate, orderid )  AS  rownum      FROM     Sales.Orders )       SELECT   * FROM     O1 WHERE    rownum  BETWEEN  11  AND  20

 

参考资料：

《SQL2008技术内幕：T-SQL语言基础》

 

 原文来自：基础很重要~~04.表表达式-上篇

作　　者： Jackson0714 
出　　处：http://www.cnblogs.com/jackson0714/ 
关于作者：专注于微软平台的项目开发。如有问题或建议，请多多赐教！ 
版权声明：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文链接。 
特此声明：所有评论和私信都会在第一时间回复。也欢迎园子的大大们指正错误，共同进步。或者直接私信我 
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是作者坚持原创和持续写作的最大动力！