1.派生表(derived table)

select YEAR(orderdate) as orderyear, COUNT(distinct custid) as numcusts

from Sales.Orders

group by YEAR(orderdate);

内联别名

select orderyear, COUNT(distinct custid) 

from (select YEAR(orderdate) as orderyear, custid from Sales.Orders) as D

group by orderyear

外部命名

select orderyear, COUNT(distinct custid) 

from (select YEAR(orderdate), custid from Sales.Orders) as D(orderyear,custid)

group by orderyear;

2.公用表表达式(CTE, common table express)

with C(orderyear, custid) as

(

select YEAR(orderdate), custid

from Sales.Orders

)

select orderyear, COUNT(distinct custid) as numcusts

from C

group by orderyear;

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

嵌套CTE

with <CTE_Name>[<target_column_list>]

as

(

<anchor_member>

union all

<recursive_member>

)

<outer_query_against_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 option(maxrecursion 5); -- 这里的引用返回所有结果集

3.视图

use TSQLFundamentals2008

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, tax

from Sales.Customers

where country = N'USA';

go

在定义表表达式的查询语句中不允许出现order by子句，因此视图定义中也不允许出现。确实须要从视图中返回有序的数据行，应该在使用视图的外部查询中指定一个数据展示用的order by子句。即使使用TOP选项，视图定义中的order by子句只是确保为TOP选项提供逻辑筛选服务，如果查询视图时没有在外部查询中指定order by子句，则仍然无法保证结果集中行的顺序。

alter view Sales.USACusts

as

select top(100) percent 

custid, companyname, contactname, contacttitle, address, city, region, postalcode, country, phone, tax

from Sales.Customers

where country = N'USA'

order by region;

go

select custid, companyname, region from Sales.USACusts; --并不能总能产生按region列进行排序的结果

encryption选项

select object_definition(object_id('Sales.USACusts')); --can get definition of the view

alter view Sales.USACusts with encryption

as

select custid, companyname, contactname, contacttitle, address, city, region, postalcode, country, phone, tax

from Sales.Customers

where country = N'USA';

go

select object_definition(object_id('Sales.USACusts')); -- can not get definition of the view

schemabinding 选项

alter view Sales.USACusts with schemabinding

as

select custid, companyname, contactname, contacttitle, address, city, region, postalcode, country, phone, tax

from Sales.Customers

where country = N'USA';

go

alter table Sales.Customers drop column address; -- error message

check option 选项

alter view Sales.USACusts with check option

as

select custid, companyname, contactname, contacttitle, address, city, region, postalcode, country, phone, tax

from Sales.Customers

where country = N'USA';

go

insert into Sales.USACusts

values (..., 'UK', ...); --error message, not 'USA'

4.内联表值函数(inline TVF, inline table-valued function)--参数化的视图

use TSQLFundamentals2008

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, shipcity, shipregion, shippostalcode, shipcountry

from Sales.Orders

where custid = @cid;

go

select C.orderid, C.custid, D.productid, D.qty

from dbo.fn_GetCustOrders(1) as C

join Sales.OrderDetails as D

on C.orderid = D.orderid;

5.Apply运算符

包括cross apply与outer apply。

cross apply与cross join类似，都是用右表表达式应用到左表中的每一行。与join不同的是，使用cross apply操作符时，对于左表中的每一行，右表表达式可能代表不同的数据行集合。为此，可以在右边使用一个派生表，在派生表的查询中去引用左表列；也可以使用内联表值函数，把左表中的列作为输入参数进行传递。

cross apply在右表为空时不显示左表的行，outer apply则显示左表的行，同时右表同一行的列用null替代。

cross 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

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