sql2008 t-sql
Sql Server四种表表达式
- 派生表:derived table. from子句中的嵌套子查询
- 视图:view
- 通用表表达式:CTE, common table expression
- 内联表值函数:intline TVF, inline table-valued function
通用表表达式CTE
CTE (Sql2005中引入)
- 使用WITH子句定义,WITH后跟CTE名称(别名)
- 外部查询完成时,生命期结束
- 由于WITH子句有不同意义,容易引入歧义,所以建议CTE语句中明确地使用分号
- 因为没有物化的存在,通常对性能没有任何影响
-- CTE, definition
with C1(orderyear, custid) as (
select year(orderdate), CustomerID
from SalesLT.SalesOrderHeader
),
C2 as (
select orderyear, count(DISTINCT custid) as numcusts
from C1
group by orderyear
)
select orderyear, numcusts
from C2
where numcusts > 30;
CTE的多引用
- 相对于派生表,因为CTE先于from子句定义,因此可以引用同一CTE的多个实例。
- 避免了像派生表那样需要维护多个子查询的副本
-- CTE, multi-reference
with YearlyCount as (
select YEAR(orderdate) as orderyear
, COUNT(distinct customerID) as numcusts
from SalesLT.SalesOrderHeader
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 join YearlyCount as Prv
on Cur.orderyear = Prv.orderyear + 1
CTE的递归
- 基本语法同CTE的一般定义。WITH内定义的查询将作为定位成员(anchor member),它只会被调用一次,返回“第一个”前一个结果集
- WITH定义内部须再添加UNION ALL关键字和一个递归查询成员(recursive member)。递归成员被调用多次,直到递归结束
- 递归成员通过引用CTE名称达到递归的目的
- 两个查询成员必须保持列个数和数据类型的兼容性
- 外部查询中对CTE名称的引用表示对该递归查询结果集的引用
- 为避免递归出现死循环,Sql server设置了最大递归次数为100,超过100时会自动终止。
- 通过在外部查询的最后指定“OPTION MAXRECURSION n”改变该限制(n=0时取消限制)
-- CTE, Recursive
WITH CategoryCTE([ParentProductCategoryID], [ProductCategoryID], [Name]) AS (
-- anchor member, executed only in the beginning
SELECT [ParentProductCategoryID], [ProductCategoryID], [Name]
FROM SalesLT.ProductCategory
WHERE ParentProductCategoryID IS NULL
UNION ALL -- to union all recursive query results
-- recursive member
SELECT C.[ParentProductCategoryID], C.[ProductCategoryID], C.[Name]
FROM SalesLT.ProductCategory AS C
INNER JOIN CategoryCTE AS BC
ON BC.ProductCategoryID = C.ParentProductCategoryID
)
SELECT PC.[Name] AS [ParentProductCategoryName]
, CCTE.[Name] as [ProductCategoryName]
, CCTE.[ProductCategoryID]
FROM CategoryCTE AS CCTE
JOIN SalesLT.ProductCategory AS PC
ON PC.[ProductCategoryID] = CCTE.[ParentProductCategoryID]
视图属性和选项
- ENCRYPTION属性:对视图、存储过程、触发器,用户定义函数(UDF)的定义进行加密,从而用sp_helptext和OBJECT_DEFINITION无法获取元数据
- SCHEMABIDING属性:指定改属性后,视图所引用的对象无法被删除,被引用的列也不可删除或修改
- CHECK OPTION选项:检查以避免通过视图进行的数据修改与当前视图中设置的过滤条件相冲突。
ALTER VIEW [SalesLT].[vEnProduct]
WITH SCHEMABINDING, encryption -- view attributes
AS
SELECT p.[ProductID]
,p.[Name]
,pmx.[Culture]
,pd.[Description]
FROM [SalesLT].[Product] p
INNER JOIN [SalesLT].[ProductModelProductDescription] pmx
ON p.[ProductModelID] = pmx.[ProductModelID]
INNER JOIN [SalesLT].[ProductDescription] pd
ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID]
WHERE pmx.Culture = 'en' -- this view only contains products for 'en'
WITH CHECK OPTION;
GO
-- Error sample: conflict with CHECK OPTION
-- Can't be changed to other culture other than 'en'
update P
set P.Culture = 'zh'
from SalesLT.vEnProduct as P
-- You can't insert a record without 'en' Culture tagged
内联表值函数(参数化视图)
除了支持输入参数以外,其他方面与视图类似,下边是一个例子:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufnGetCustomerInformation]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufnGetCustomerInformation]
GO
CREATE FUNCTION [dbo].[ufnGetCustomerInformation]
(@CustomerID int) RETURNS TABLE
AS
RETURN (
SELECT
CustomerID,
FirstName,
LastName
FROM [SalesLT].[Customer]
WHERE [CustomerID] = @CustomerID
APPLY运算符(APPLY opeator)
- Sql2005引入的非标准运算符
- 在FROM子句中使用
- 包括CROSS APPLY和OUTER APPLY两种形式,概念上相似于INNER JOIN和OUTER JOIN。
基本原理如下:
- APPLY运算符以两个输入表为左右参数,其中右表(第二个表),可以是表表达式,通常是派生表或内联表值函数。
- CROSS APPLY:把右表应用到左表的每一行,再把结果集组合起来,输出统一的表结果。如果右表为空,则对应的左表行不会输出。
- OUTER APPLY:比CROSS APPLY多了一个逻辑处理步骤,标示出让右表为空的左表数据行,输出该行是,右表的列将置为NULL。
- 出于封装的目的,推荐使用内联表值函数取代派生表作为右表
-- Get the first 3 orders of each customer
select C.CustomerID
, A.SalesOrderID
, A.OrderDate
from SalesLT.Customer as C
outer apply (
select top(3) SalesOrderID, OrderDate
from SalesLT.SalesOrderHeader as O
where O.CustomerID = C.CustomerID
order by OrderDate desc, SalesOrderID desc
) as A
order by C.CustomerID
分享到:
相关推荐
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》是一本深入探讨SQL Server 2008中T-SQL(Transact-SQL)查询的权威指南。T-SQL是微软SQL Server数据库管理系统的核心语言,用于数据操作、查询、存储过程编写以及...
作为一本讲述T-SQL高级查询的专业图书,《Microsoft SQL Server 2008技术内幕:T-SQL查询》旨在结合实践中的各种常见问题,教读者如何用SQL作为语言工具来思考问题,揭示基于集合查询的强大威力。《Microsoft SQL ...
本书全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化、查询算法及复杂度,以及在使用子查询、表...
《Microsoft SQL Server 2005技术内幕:T-SQL查询的源代码》是一本深入探讨SQL Server 2005中T-SQL查询技术的专业书籍。T-SQL(Transact-SQL)是Microsoft SQL Server数据库管理系统中用于数据操作、查询、存储过程...
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》是Microsoft SQL Server 2008系列中的一本。书中全面深入地介绍了T-SQL的基本元素,以及SQL Server 2008中新增加的一些特性。主要包括SQL的基础理论、逻辑...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》是一本深入探讨SQL Server 2008中T-SQL(Transact-SQL)查询的权威书籍。T-SQL是微软SQL Server的主要编程语言,用于管理和操作数据库,执行查询、数据更新以及...
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》是Microsoft SQL Server 2008系列中的一本。书中全面深入地介绍了T-SQL的基本元素,以及SQL Server 2008中新增加的一些特性。主要包括SQL的基础理论、逻辑查询...
《Microsoft SQL Server 2005技术内幕全套(三):T-SQL查询》是一部深入探讨SQL Server 2005数据库管理系统中Transact-SQL(T-SQL)查询技术的专业著作。本部分主要聚焦于如何高效、准确地在SQL Server 2005环境中...
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》是关于数据库管理和开发的重要参考资料,专注于T-SQL(Transact-SQL)这一SQL Server的核心编程语言。T-SQL是微软SQL Server中用于数据查询、更新、插入和删除...
《SQLServer2008技术内幕:T-SQL查询》这本书深入探讨了SQL Server 2008中的Transact-SQL(T-SQL)查询语言,这是SQL Server数据库管理系统的核心部分,用于数据检索、更新、插入和删除。T-SQL不仅包含标准的SQL语法...
全球公认SQL Server 2005经典著作,囊括大量鲜为人知的技术内幕,大师智慧、专家经验尽览无余。 本系列图书中文版得到了微软总部SQL Server组专家的高度重视,同时也得到了微软中国上海SQL Server全球技术支持...
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》是Microsoft SQL Server 2008系列中的一本。书中全面深入地介绍了T-SQL的基本元素,以及SQL Server 2008中新增加的一些特性。主要包括SQL的基础理论、逻辑...
《Microsoft SQL Server 2005技术内幕:T-SQL查询》是一本专注于SQL Server 2005数据库管理系统中T-SQL(Transact-SQL)查询语言的专著。这本书是技术内幕系列的一部分,旨在为读者提供深入、全面的T-SQL查询知识,...
### Microsoft SQL Server 2008技术内幕:T-SQL查询 #### 书籍概述 《Microsoft SQL Server 2008技术内幕:T-SQL查询》是一本深入探讨Microsoft SQL Server 2008中T-SQL查询及其性能优化的专业书籍。本书由Lubor ...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》是一本深入探讨SQL Server 2008中T-SQL(Transact-SQL)查询的权威指南。T-SQL是微软数据库管理系统的核心,用于数据操作、定义、存储过程和触发器等任务。本书...
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》这本书是针对SQL Server 2008数据库管理系统,特别关注其Transact-SQL(T-SQL)语言的全面指南,非常适合初学者深入理解T-SQL的基础知识。T-SQL是SQL Server的...
本书及其续篇——《Microsoft SQL Server 2005技术内幕:T-SQL程序设计》介绍了SQL Server 2005中高级T-SQL查询、查询优化及编程相关的知识。这两本书侧重于解决实践中的常见问题,并讨论了解决这些问题的方法。它们...
《MicrosoftSQLServer2008技术内幕》是Microsoft SQL Server 2008系列中的一本。书中全面深入地介绍了T-SQL的基本元素,以及SQL Server 2008中新增加的一些特性。主要包括SQL的基础理论、逻辑查询处理、SELECT查询、...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...