Microsoft SQL Server 2008技术内幕：T-SQL语言基础（表表达式）

五、表表达式

• 表表达式
  • 将表达式的查询结果作为表进行使用
• 分类
  • 派生表 derived table
  • 公用表表达式 common table expression, CTE
  • 视图
  • 内联表值函数 inline table-valued function inline, TVF
• 特点
  • 物理上并非真实存在
  • 数据库引擎内部都将转换为底层对象的查询
  • 性能上基本没有差异
• 作用
  • 简化代码逻辑
• 要求
  • 不保证有一定的顺序
    • 表达式中不允许出现 order by
  • 所有的列必须有名称
  • 所有的列名必须是唯一的

5.1 派生表

• 定义派生表
  • 在圆括号内，后面跟着 as 子句和派生表的名称

select * 
from (SELECT custid,ordermonth,qty
	FROM Sales.CustOrders) as CustOrders

5.1.1 分配列别名

• 内联别名

select orderyear, count(distinct custid) as numcusts 
from (SELECT year(orderdate) as orderyear,custid
	FROM Sales.Orders) as D
group by orderyear;

• 外联别名

select orderyear, count(distinct custid) as numcusts 
from (SELECT year(orderdate),custid
	FROM Sales.Orders) as D(orderdate,custid)
group by orderyear;

5.1.2 嵌套

• 容易出问题

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;

5.1.3 派生表的多引用

• 当需要多次引用同一个派生表时需要重复定义

select cur.orderyear,
	cur.numcusts as curnumcusts,prv.numcusts as prvnumcusts,
	cur.nmcusts - 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 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 公用表表达式

• ANSI

5.2.1 定义格式

with usacusts /*CTE_name*/ as
(
	-- inner_query_defining_CTE
	select custid, companyname
	from sales.customers
	where countr ='USA'
)
-- outer_query_against_CTE
select * from usacusts;

5.2.2 不需要嵌套

with D1 AS
(
	SELECT year(orderdate) as orderyear,custid
	FROM Sales.Orders
),
D2 AS
(
	select orderyear, count(distinct custid) as numcusts 
	from D1
	group by orderyear
)
select orderyear,numcusts
from D2 where numcusts >70;

5.2.3 多引用不需要重复

• 每个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 curnumcusts,prv.numcusts as prvnumcusts,
	cur.nmcusts - prv.numcusts as growth
from yearlycount as cur
left join yearlycount as prv
on cur.orderyear = prv.orderyear + 1;

5.2.4 递归

• 至少包含两个查询
  • 定位点成员
  • 递归成员
• 对【CTE_name】的引用代表的是在上一个执行序列中的结果集
  • 第一次调用递归成员时，其中对【CTE_name】的引用代表的是定位点成员的执行结果
• 定位点成员只被调用一次
• 递归查询的中间结果保存在temp数据库中

-- 查询Id=2的员工及其直接下属和间接下属
with empscte as
(
	-- anchor_member
	-- 只被调用一次
	select empid,mgrid,firstname,lastname
	from hr.employees
	where empid=2
	
	union all

	-- recursive_member
	-- 对CTE_name的引用代表的是在上一个执行序列中的结果集
	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;

• 最大递归调用次数
  • SQL Server默认为100
  • 可在外部查询的最后设置最大递归次数
    • 0为不限制

-- 查询Id=2的员工及其直接下属和间接下属
with empscte as
(
	-- anchor_member
	-- 只被调用一次
	select empid,mgrid,firstname,lastname
	from hr.employees
	where empid=2
	
	union all

	-- recursive_member
	-- 对CTE_name的引用代表的是在上一个执行序列中的结果集
	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 option(maxrecursion 200);

5.3 视图

• 存储在数据库中，可复用
• 视图的列是在编译视图时进行枚举的，新增加的列不会自动加到视图中
• sp_refreshview
  • 当视图是通过select * 创建的，可以通过该存储过程刷新元数据

5.3.1 视图的定义

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 ='USA';
go

5.3.2 视图选项

5.3.2.1 encryption

• 默认可以查询定义视图的文本，即create view …
  • select object_definition(object_id(‘sales.usacusts’));
• 指定encryption后，则无法查看

alter view sales.usacusts with encryption
as
select custid,companyname, contactname, contacttitle
	, address,city,region,postalcode,country,phone,fax
from sales.customers
where country ='USA';
go

5.3.2.2 schemabinding

• 将对象和列的架构绑定到引用其对象的架构
  • 即被引用的对象不能删除，被引用的列也不能删除或修改
• 要求
  • 视图不是使用select * 进行查询
  • 对象名称为完整对象名称

alter view sales.usacusts with schemabinding
as
select custid,companyname, contactname, contacttitle
	, address,city,region,postalcode,country,phone,fax
from sales.customers
where country ='USA';
go

5.3.2.3 check option

• 默认情况下，可以向视图中插入不满足视图过滤条件的数据
• 设置check option后，则不允许

alter view sales.usacusts with schemabinding
as
select custid,companyname, contactname, contacttitle
	, address,city,region,postalcode,country,phone,fax
from sales.customers
where country ='USA'
with check option;
go

5.4 内联表值函数

• 可复用的表表达式
• 可支持输入参数，可看作参数化的视图

if object_id('dbo.fn_getcustorders') is not null
	drop function dbo.fn_getcustorders;
go
create function dbo.fn_getcustorders
(@cid as int) returns table
as
return
	select orderid,custid,empid,orderdate
	from sales.orders
	where custid = @cid ;
go

select orderid,custid
from dbo.fn_getcustorders(1) as co;

5.5 apply运算符

• 效果类似 cross join
• 区别
  • apply中右表表达式可以是相关子查询
    • 根据左表中的行动态生成右边的派生表
  • join中右表表达式必须是独立子查询
• 分类
  • cross apply
    • 如果右表表达式返回空集，则左表相应数据不显示
  • outer apply
    • 如果右表表达式返回空集，则左表相应数据会作为外部行添加到结果中
      • 相当于外联接

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;