第五章 表表达式(1)

--表表达式
--5.1 派生表
--5.1.1 分配列别名
--满足三个要求 
--不能定义order by
--所有的列必须有名称
--所有的列必须是唯一的
use TSQLFundamentals2008;
select * from 
(select custid, companyname
from Sales.Customers
where country = N'USA') as usacusts;

--在外部查询的任何子句都可以引用在内部查询的select子句分配的列别名
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(orderyear, custid)
group by orderyear

--5.1.2 使用参数
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

--5.1.3 嵌套
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, prv.numcusts as prvnumcusts,
cur.numcusts - 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 outer 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 公用表表达式(CTE)
with USAcasts as 
(select custid, companyname
from Sales.Customers
where country=N'USA'
)
select * from USAcasts;

--5.2.1 分配列别名
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;

--5.2.2 使用参数
declare @empid1 as int 
set @empid1 = 3;

with C as 
(select YEAR(orderdate) as orderyear, custid
from Sales.Orders
where empid=@empid1)
select orderyear, COUNT(distinct custid) as numcusts
from C
group by orderyear;

--5.2.3 定义多个CTE
--每个CTE可以引用在它前面定义的所有CTE,而外部查询则可以引用所有CTE
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
where numcusts > 70

--5.2.4 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.numcusts - prv.numcusts as growth
from yearlycount as cur
left outer join
yearlycount as prv
on cur.orderyear=prv.orderyear+1;

--5.2.5 递归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

--5.3 视图
--视图和内联表值函数(inline TVF)是两种可重用的表表达式，它们的定义存储在一个数据库对象中。
--一旦创建，这些对象就是数据库的永久部分；只有用删除语句显式删除，它们才会从数据库中移除。
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=N'USA';
go

select custid, companyname
from Sales.usacusts

--5.3.1 视图和ORDER BY子句
--试图创建一个有序视图的想法也不合理,因为这违反了关系模型定义的关系的基本属性
--5.3.2 视图选项
--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=N'USA';
go

exec sp_helptext N'sales.usacusts' --The text for object 'sales.usacusts' is encrypted.

select OBJECT_DEFINITION(Object_id(N'sales.usacusts')); --NULL

--schemabinding选项可以将对象和列的架构绑定到引用其对象的架构。也就是说，一旦指定了这个选项，
--被引用的对象就不能删除，被引用的列也不能删除或修改
alter view sales.usacusts 
with schemabinding, encryption
as
select custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
from Sales.Customers
where country=N'USA';
go

--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=N'USA'
with check option;

insert into Sales.usacusts
values('Customer111', 'Contact FGHJK', 'Title HGHG', 'Address',
'City', 'region', '111', 'USA', '012-345678', '012-345678')