表表达式

大家好，欢迎来到IT知识分享网。———————————————————————————————————

–表表达式
———————————————————————————————————
–派生表
–公用表表达式(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类似于左外联接
–可以返回没有下过订单的客户