第8章 高级查询(二).ppt

* 备注：因为就外部查询而言，它的查询对象是一个名为O的表，这个表有两列：OrderYear和UserName。 * * * * CTE是在SQL Server2005中引入的，是ANSI SQL（1999及以后版本）标准的一部分，具有一些重要的优势。 * * 备注：从表面来看，派生表和CTE之间的区别可能只是语义方面。 * 表表达式通常对性能没有任何影响，因为他们没有任何物化的存在。 * * EXISTS语句我们并不陌生，我们在学习建库和建表语句时曾提前用过， 它是一个存在检测的子查询语句 。 * * * * * 第8章 课程回顾 什么是独立子查询？ 独立子查询的注意事项？ 什么是相关子查询？ * 本章节授课目标 掌握派生表的使用 掌握公用表表达式 * 表表达式 表表达式是一种命名的查询表达式，代表一个有效的关系表 SQL Server支持4中类型的表表达式： 派生表(derived table) 公用表表达式(CTE,common table expression) 视图 内联表值函数(inline table-valued function) 派生表 派生表（也称为表子查询）是在外部查询的FROM子句中定义的 派生表的存在范围为定义它的外部查询，只要外部查询一结束，派生表也就不存在了 定义派生表的查询语句要写在一对圆括号内，后便跟着AS子句和派生表的名称 * 派生表示例 SELECT * FROM (SELECT UserName,Email,RegDate,ActiveDate,Balance FROM dbo.TBa_MemberInfo WHERE Status=1) AS ActiveMember; 编写T-SQL查询，返回所有处于使用状态的用户信息 注意事项 查询结果不保证有一定的顺序，除非查询语句带有TOP和ORDER BY子句 所有的列必须有名称，必须为SELECT列表中的表达式起别名 所有的列名必须是唯一的 * 分配列别名-1 使用派生表的一个好处是：在外部查询的任何子句中都可以引用在内部查询的SELECT子句中分配的列别名 编写T-SQL查询，返回订单表中每个年份处理过的不同客户的总数 出现问题的原因：在GROUP BY 子句中引用了一个在SELECT子句中定义的列的列别名，而GROUP BY子句在逻辑上是先于SELECT子句处理的 --以下尝试是无效的 SELECT YEAR(OrderTime) AS OrderYear,COUNT(DISTINCT UserName) AS NumUsers FROM dbo.TMe_OrderInfo GROUP BY OrderYear; * 分配列别名-2 使用派生表 SELECT OrderYear AS 年份,COUNT(DISTINCT UserName) AS 会员个数 FROM (SELECT YEAR(OrderTime) AS OrderYear,UserName FROM dbo.TMe_OrderInfo) AS O GROUP BY OrderYear; 派生表 这段代码定义了一个名为O的派生表 内部查询的SELECT列表中将表达式YEAR(OrderTime) 命名为OrderYear 外部查询在GROUP BY 和SELECT子句中都可以引用这个OrderYear列别名 SELECT YEAR(OrderTime) AS 年份,COUNT(DISTINCT UserName) AS 会员个数 FROM dbo.TMe_OrderInfo GROUP BY YEAR(OrderTime); 如果表达式很长怎么办？ * 使用参数 在定义派生表的查询中，可以引用参数。 参数可以是局部变量和例程(如存储过程和函数）的输入参数 DECLARE @OperID AS varchar(6)=100; SELECT OrderYear AS 年份,COUNT(DISTINCT UserName) AS 会员名称 FROM (SELECT YEAR(OrderTime) AS OrderYear,UserName FROM dbo.TMe_OrderInfo WHERE OperID=@OperID) AS O GROUP BY OrderYear; GO * 嵌套-1 如果需要用一个本身就引用了某个派生表的查询去定义另一个派生表，最终得到的就是嵌套派生表。 嵌套让代码变得复杂，降低代码的可读性 SELECT OrderYear AS 年份,NumUsers AS 会员个数 FROM (SELECT OrderYear,COUNT(DISTINCT UserName) AS NumUs