文档章节

《SQLSERVER2012之T-SQL教程》T-SQL表的表达式

XuePeng77
 XuePeng77
发布于 2016/11/17 21:57
字数 2501
阅读 11
收藏 0

表结构与数据:https://github.com/XuePeng87/TSQLV4

    表的表达式(Table Expression)是一个命名的查询表达式,MSSQL支持4种类型的表表达式:派生表、公用表表达式(CTE)、视图和内嵌表值函数(TVF)。

派生表

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

    上面的例子是一个基本语法的简单示例。由于外部查询没有应用任何操作,派生表实际上是不需要的。

    有效定义任何类型表表达式的查询必须满足3个要求:

  1. 无法保证顺序;
  2. 所有列都必须具有名称;
  3. 所有列明都必须是唯一的;

分配列别名

    使用表表达式的好处之一就是,在外部查询的任何子句中,可以引用内部查询的SELECT子句中分配的列别名。例如,假设需要对Sales.Orders表编写一个查询,返回每个订单年度处理的非重复客户数量。由于GROUP BY子句引用了在SELECT子句中分配的列别名,并且GROUP BY子句是在SELECT子句之前处理的,所以下面的尝试无效:

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

消息 207,级别 16,状态 1,第 5 行
列名 'orderyear' 无效。

    这时,可以使用内嵌别名形式的派生表查询:

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

    一般来说,表表达式没有正面或负面的性能影响。

使用参数

DECLARE @empid AS INT = 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;

嵌套

    如果定义派生表查询需要引用另一个派生表,这就是一个嵌套派生表。嵌套是常用的编程问题,由于它的代码复杂,降低了代码的可读性。

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 Cur.orderyear, Cur.numcusts AS curnumcusts, Pre.numcusts AS prenumcusts, 
Cur.numcusts - Pre.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 Pre 
ON Cur.orderyear = Pre.orderyear + 1

公用表表达式

    公用表表达式(CTE)是表表达式的另一种标准形式,与派生表非常类似。定义CTE的内部查询必须遵循前面提到的定义表表达式的所有要求,例如:

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

在CTE中分配列别名

    CTE也支持两种列别名的命名方式——内嵌方式和外部方式。

    下面是内嵌方式的实例:

WITH C AS 
(
	SELECT YEAR(orderdate) AS orderyear, custid 
	FROM Sales.Orders 
) 
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts 
FROM C GROUP BY orderyear;

    下面是外部方式的实例:

WITH C(orderyear, custid) AS 
(
	SELECT YEAR(orderdate), custid 
	FROM Sales.Orders 
) 
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts 
FROM C GROUP BY orderyear;

在CTE中使用参数

DECLARE @empid AS INT = 3;

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

定义多个CTE

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

    从技术上来看,不能嵌套CTE,也不能在派生表的括号内定义CTE,但是嵌套是一种较为复杂的做法,因此,可以说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 curmuncusts, Pre.numcusts AS prvnumcusts, 
Cur.numcusts - Pre.numcusts AS growth  
FROM YearlyCount AS Cur 
LEFT OUTER JOIN YearlyCount AS Pre 
ON Cur.orderyear = Pre.orderyear + 1

    这段代码比之前引用多个派生表的代码清晰了不少,只定义了一次YearlyCount。

递归CTE

    CTE具有递归能力,在表表达式之间CTE应是唯一的。递归CTE至少由两个查询定义,至少一个查询作为定位点成员,一个查询作为递归成员。举个例子,使用递归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;

    注意,放置无限递归的安全措施是,MSSQL默认情况下限制递归成员可以被调用的次数为100,递归成员的第101次将会失败,可以在外部查询的尾部指定OPTION(MAXRECURSION n)提示来更改默认的最大递归限制,n是一个0~32767范围的证书,如果希望完全取消限制,可以让n=0。如果取消了限制,工作表将会很快变得很大。如果tempdb不能增长了,例如,磁盘空间用完了,查询将失败。

视图

    派生表和CTE具有非常有限的范围,只要查询完成了,它们就消失了。

    视图和内嵌表值函数(内嵌TVF)是两种可重复使用的表表达式类型,它们被存储为数据库对象。创建之后这些对象是数据库的永久部分,只有显示删除他们才从数据库中移除。

IF OBJECT_ID('Sale.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

视图与ORDER BY子句

    用于定义视图的查询必须满足在之前提到的派生表中的所有要求,视图无法保证行的顺序,相反,你应该在对视图的外部查询中指定一个展示用的ORDER BY子句:

SELECT custid, companyname, region 
FROM Sales.USACusts
ORDER BY region;

视图选项

1.ENCRYPTION选项

    在创建或更改视图、存储过程、触发器和用户定义函数时,ENCRYPTION选项是可用的。它指示MSSQL在内部以代码混淆方式存储对象定义文本。代码混淆文本对通过任何目录对象的用户不直接可见,仅对通过特定方法的特权用户可见。例如未加ENCRYPTION的视图可以通过该SQL语句查询到:

SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));

    下面修改一下Sales.USACusts视图:

ALTER VIEW Sales.USACusts WITH ENCRYPTION
AS 
SELECT custid, companyname, contactname, contacttitle, 
address,city, region, postalcode, country, phone, fax 
FROM Sales.Customers 
WHERE country = N'USA' 
GO

    在使用OBJECT_DEFINITION就查询不到该视图了。

2.SCHEMABINDING选项

    该选项对视图和UDF可用,它将被引用对象的架构和列绑定到引用对象的架构中。指示不能删除被引用对象,也不能删除或修改被引用的列。

    例如,把Sales.USACusts加入SCHEMABINDING选项:

ALTER VIEW Sales.USACusts WITH SCHEMABINDING
AS 
SELECT custid, companyname, contactname, contacttitle, 
address,city, region, postalcode, country, phone, fax 
FROM Sales.Customers 
WHERE country = N'USA' 
GO

    现在尝试删除Customers表中address列将会出现错误提示:

ALTER TABLE Sales.Customers DROP COLUMN address;

消息 5074,级别 16,状态 1,第 1 行
对象'USACusts' 依赖于 列'address'。
消息 4922,级别 16,状态 9,第 1 行
由于一个或多个对象访问此列,ALTER TABLE DROP COLUMN address 失败。

    使用SCHEMABINDING,要求查询语句中不可使用*,而且必须显示地列出列名。此外,在引用对象时,必须使用架构限定的两部分名称。

3.CHECK OPTION选项

    CHECK OPTION的目的是防止出现视图修改与视图筛选的冲突,假设已存在一个视图查询定义,查询USACusts,用于筛选国家为USA的客户,视图当前没有定义CHECK OPTION。这意味着你可以通过视图INSERT来自除美国以外的国家的客户,以及可以通过视图更改现有客户的国家为美国以外的国家:

INSERT INTO Sales.USACusts(companyname, contactname, contacttitle, 
address,city, region, postalcode, country, phone, fax) VALUES 
(N'Customer ABCED', N'Contact ABCDE', N'Title ABCDE', N'Address ABCED', 
N'London', NULL, N'12345', N'UK', N'012-23456789', N'012-3456789')

(1 行受影响)

    通过修改视图,在尾端加入WITH CHECK OPTION来做限制后,在执行INSERT就会提示错误了:

ALTER VIEW Sales.USACusts WITH SCHEMABINDING
AS 
SELECT custid, companyname, contactname, contacttitle, 
address,city, region, postalcode, country, phone, fax 
FROM Sales.Customers 
WHERE country = N'USA' 
WITH CHECK OPTION
GO
INSERT INTO Sales.USACusts(companyname, contactname, contacttitle, 
address,city, region, postalcode, country, phone, fax) VALUES 
(N'Customer ABCED', N'Contact ABCDE', N'Title ABCDE', N'Address ABCED', 
N'London', NULL, N'12345', N'UK', N'012-23456789', N'012-3456789')

消息 550,级别 16,状态 1,第 1 行
试图进行的插入或更新已失败,原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION,而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。
语句已终止。

内嵌表值函数

    内嵌TVF是支持输入参数的可重复使用的表表达式,也可以看作是可以传参查询的视图:

IF OBJECT_ID('') IS NOT NULL
	DROP FUNCTION dbo.GetCustOrders;
GO
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;
GO

    使用TVF查询:

SELECT orderid, custid 
FROM dbo.GetCustOrders(1) AS O;

APPLY运算符

    APPLY运算符是一个非常强大的表运算符。APPLY运算符支持的两个类型是CROSS APPLY和OUTER APPLY。

    CROSS APPLY仅实施一个逻辑查询处理阶段,而OUTER APPLY实施了两个阶段。

    APPLY不是标准的,相对应的标准叫做LATERAL,但是此标准未在MSSQL中实现。

    APPLY运算符对两个输入表进行操作,第二个表可以是一个表表达式,右侧的表可以对来自左侧表的每一行表示一个不同的行集,例如,下面代码返回每个客户的3个最近订单:

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;

    OUTER APPLY运算符表示右侧表为空的行:

SELECT C.custid, A.orderid, A.orderdate  
FROM Sales.Customers AS C 
	OUTER 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;

 

© 著作权归作者所有

XuePeng77
粉丝 48
博文 146
码字总数 194285
作品 0
丰台
私信 提问
《SQLSERVER2012之T-SQL教程》T-SQL单表查询(二)

表结构与数据:https://github.com/XuePeng87/TSQLV4 谓词和运算符 1) 谓词 T-SQL支持的谓词例子包括IN、BETWEEN和LIKE。 IN可以检查一个值或是一个标量表达式是否至少等于集合中的一个元素。...

杰克鹏仔
2016/11/09
18
0
2.《SQLSERVER2012之T-SQL教程》T-SQL单表查询(二)

表结构与数据:https://github.com/XuePeng87/TSQLV4 谓词和运算符 1) 谓词 T-SQL支持的谓词例子包括IN、BETWEEN和LIKE。 IN可以检查一个值或是一个标量表达式是否至少等于集合中的一个元素。...

巧乐兹
2016/10/25
5
0
《SQLSERVER2012之T-SQL教程》T-SQL单表查询(一)

表结构与数据:https://github.com/XuePeng87/TSQLV4 SELECT语句的元素 SELECT语句的用途的查询表,通过一些逻辑操作来返回一个结果。例如: 此查询的意义是: 筛选客户71名下的订单,按雇员...

杰克鹏仔
2016/11/09
13
0
1.《SQLSERVER2012之T-SQL教程》T-SQL单表查询(一)

表结构与数据:https://github.com/XuePeng87/TSQLV4 SELECT语句的元素 SELECT语句的用途的查询表,通过一些逻辑操作来返回一个结果。例如: 此查询的意义是: 筛选客户71名下的订单,按雇员...

巧乐兹
2016/10/24
15
0
T-SQL基础(一)之简单查询

名词解释 SQL: Structured Query Language,结构化查询语言,是一种在关系型数据库中用于管理数据的标准语言。SQL是一种声明式编程语言,即只需表明需要什么而无需关注实现细节(C#中的LINQ也...

雪飞鸿
2018/11/02
0
0

没有更多内容

加载失败,请刷新页面

加载更多

java快递电子面单打印接口对接demo

之前的后天管理系统的电子面单打印使用的是灵通打单。 使用相对比较麻烦,需要到处Excel之后再导入,麻烦。 快递鸟有电子面单api,后台系统直接对接很是方便,不过也遇到了好些问题。 不难是...

程序的小猿
27分钟前
3
0
fasjtjson文档

https://github.com/alibaba/fastjson/wiki/JSONField

jirak
28分钟前
3
0
Mybatis中插入多条记录

Oracle数据库 实现方法 <insert id="saveWithdrawLog"> INSERT ALL INTO OSM_TRADE_DETAIL(SID,MBR_ID,USR_ID,TRADE_MONEY,TRADE_TYPE,TRADE_TIME,TRADE_WAY,PAY_ID) VALUES(#{si......

豫华商
28分钟前
3
0
Flink on YARN(下):常见问题与排查思路

作者:杨弢(搏远) Flink 支持 Standalone 独立部署和 YARN、Kubernetes、Mesos 等集群部署模式,其中 YARN 集群部署模式在国内的应用越来越广泛。Flink 社区将推出 Flink on YARN 应用解读...

开源中国小二
30分钟前
3
0
技术沙龙|京东云端到端多媒体关键技术揭秘

编者按:从带来更高编码效率、更好的用户体验的京享高清,到直播架构与网络演进优化,从而为用户带来更流畅的观看体验,以及运维系统的异常自动修复和高弹性的多媒体存储架构,一层一层展示出...

京东云技术新知
30分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部