表表达式

---------------------------------------------------------------------------------------------------------
--表表达式
---------------------------------------------------------------------------------------------------------
--派生表
--公用表表达式(CTE)
--视图
--内联表值函数
--APPLY运算符（联接表表达式,(2005引入的非标准表运算符)）


--重点：
--1、使用表表达式的好处通常体现在代码的逻辑方面，而不是性能方面。
--2、定义表表达式的查询语句中不允许出现order by子句，可以使用视图的外部查询中使用。




--*****************************************************************
--1派生表(可进行分页)
--*****************************************************************
--将结果集作为数据源
--语法：
select * from (结果集) as 表名[(列名)]
--例子：
select * from
(
values(1,'张三','男'),
(2,'小莉','女')
)as tabl(id,name,sex)



--分页方式一，使用Top（SQL2000常用） 
-------------------------------------------------------
--(m是页数，n是条数)
select top n
* 
from 
MyTestDB..Student1 
where 
stuId not in 
(
select top ((m-1)*n) stuId from test1..Student order by stuId
)
order by
stuId;
/*
--显示第2页，每页10条数据
m页数 n显示条数
1 1-10
2 11-20
3 21-30
n (n-1)*10
*/

--分页方式二：使用Row_number()（SQL2005以后常用） 
-------------------------------------------------------
--结果集
select 
ROW_NUMBER() over(order by stuId) as num
, *
from
test1..Student;
----------------------------------------------
--（m是页数，n是条数）
select 
*
from 
(
select
ROW_NUMBER() over(order by stuId) as num
, *
from
MyTestDB..Student1
) as tbl
where
tbl.num between (m - 1) * n + 1 and m * n;
/*
--每页显示10条
m是页数 n是条数
1 1-10
2 11-20
3 21-30
m (m-1)*10+1——m*10
*/


--*****************************************************************
--2公用表表达式(CTE)
--*****************************************************************
--使用派生类表会造成肚子非常大，头和脚太小，因此产生公用表表达式（CTE）
--语法1：CTE分配列别名（外部格式）
with 别名[(字段)]
as
(
结果集
)
select * from 别名 where 条件
--语法2：CTE分配列别名（内联格式）
with 别名
as
(
结果集
)
select * from 别名 where 条件

--例子1
--在同一个with子句中定义多个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

--例子2递归
--使用递归CTE来返回有关某个雇员（empid=2）及其所有各级下属的信息：
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
option(maxrecursion 1000);
--默认递归调用次数为100次，用option可以设置0-32767之间的整数，0代表不限制次数
--第一次调用递归成员，返回雇员2的直接下属：雇员3和5；
--第二次调用递归成员，返回雇员3和5的直接下属：雇员4、6、7、8、9；
--第三次调用递归成员时，没有下级雇员，递归返回一个空集，递归结束
--例子3递归
With recursive_CTE(EmployeeID,ManagerID,Title,LoginID,TLevel)
as
(
--基本语句
select EmployeeID,ManagerID,Title,LoginID,0 as TLevel
from HumanResources.Employee
where ManagerID is null
union all
--递归语句
select e.EmployeeID,e.ManagerID,e.Title,e.LoginID,eb.Title+1 as TLevel
from HumanResources.Employee e 
inner join recursive_CTE eb on e.ManagerID=eb.EmployeeID
)
select * from recursive_CTE
option(maxrecursion 2)--限制递归次数为2次


--*****************************************************************
--3视图（又称批处理,要用go），视图可以修改，但是不能修改关联的字段和外键字段
--*****************************************************************
--语法
use test1
go
create view vw_视图名
as
结果集
go
--查询
select * from dbo.vw_视图名;
--好处:简单、快捷、安全（视图和架构合用才能体会到安全）



--用视图进行分页
-------------------------------------
--例子
go
create view vw_FenYe
as
select 
ROW_NUMBER() over(order by t1.stuId) as stuId
--t1.stuId
, t1.stuName
, case when t1.stuSex = 'f' then '女' else '男' end as stuSex
, datediff(year, t1.stuBirthdate, GETDATE()) as stuAge
, t1.stuAddress
, t1.stuPhone
, t1.stuEmail
--, t1.stuStudydate
, t3.className
, t2.level
, t3.classDescription
from
TestDataBase..Student as t1
inner join
(select stuId, avg((testBase + testBeyond + testPro) * 1. / 3) as level from TestDataBase..Score group by stuId) as t2
on t1.stuId = t2.stuId
inner join
TestDataBase..Course  as t3
on t1.classId = t3.classId
where
t1.stuIsDel = 0;
go


select * from vw_FenYe where stuId between 1 and 10;




--创建视图
-------------------------------------------
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 * FROM sALES.USACusts


--调用object_definition函数可以得到视图的定义
select OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'))
--或者使用sp_helptext存储过程获取视图的定义
exec sp_helptext 'Sales.USACusts';




--视图的三个重要选项
--------------------------------------------------------------------------
--1、encryption选项
--添加with 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


--2、schemabinding选项
--添加with schemabinding选项后，视图关联的表及列不能随意的删除；
--添加选项要满足两个条件：
--1）不允许在查询select子句中使用*号；
--2）在引用对象时，必须使用带有架构名称修饰的完整对象名称；
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


--3、check option选项
--添加with check option选项后，不能向视图中插入和视图过滤条件相冲突的数据;
--也就是不能在通过视图插入“不是USA国家的”数据了，只能通过表插入
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


--*****************************************************************
--4内联表值函数（可传参数的视图，可返回任何类型，如NVARCHAR(400)、table）
--*****************************************************************
--语法
use test1
go
create function fc_视图名
(@变量名 as 类型,...) returns table(或者nvarchar(400))
as
return
结果集
go

--例子：创建带参数的内联表值函数，（可以理解为带参数的视图）
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,requireddate,
shippeddate,shipperid,freight,shipname,shipaddress,
shipregion,shippostalcode,shipcountry
from sales.Orders
where custid=@cid;
go


--用表值函数创建分页
-------------------------------------
--例子：
use test1
go
create function fc_SpiltPage
(@Index as int,@Count as int) returns table
as
return
--（m是页数，n是条数）
select 
*--这里不允许用*号，改成字段名称
from 
(
select
ROW_NUMBER() over(order by stuId) as num
, *--这里不允许用*号，改成字段名称
from
test1..Student
) as tbl
where
tbl.num between (@Index - 1) * @Count + 1 and @Index * @Count
go
--查询
select * from fc_SpiltPage(4,10)


--*****************************************************************
--5APPLY运算符(2005引入的非标准表运算符)
--*****************************************************************


--1、cross apply类似于交叉联接（cross join）
--好处是：可以在右边表使用一个派生表，在派生表的查询中去引用左表列；
--也可以使用内联表值函数，把左表中的列作为参数进行传递
--以下是使用corss 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;


--2、outer apply类似于左外联接
--可以返回没有下过订单的客户