第四章
子查询:在外部查询内嵌套的内部查询（按照期望值的数量分为，标量子查询 scalar subqueries，多值子查询multivalued subqueries）（按照子查询对外部查询的依赖性分为独立子查询self-contained subqueries和相关子查询 correlated subqueries）
应用一：关系分区问题，使用group by和distinct count 来解决关系分区问题
Eg：NorthWind返回每个美国员工至少为其处理过一个订单的所有客户
独立子查询，逻辑上，可以只为整个外部查询计算一次。物理上，查询优化器会考虑不同的方法来完成相同的任务。
selectCustomerID
fromdbo.Orders
whereEmployeeIDin
(selectEmployeeIDfromdbo.EmployeeswhereCountry=N'USA')
groupbyCustomerID
havingCOUNT(distinctEmployeeID)=
(selectCOUNT(*)fromdbo.EmployeeswhereCountry=N'USA')
应用二：每个月最后日期发生的订单
selectOrderID,CustomerID,EmployeeID,OrderDate
fromdbo.orders
whereOrderDatein
(selectMAX(OrderDate)
fromdbo.Orders
groupbyCONVERT(char(6),OrderDate,112));
相关子查询，逻辑上，子查询会为外部查询的每一行都计算一次，物理上他是一个动态的过程，随情况的变化会有所不同。
附加属性（tiebreaker）问题
Eg：
方案一，基于子查询
createuniqueindexidx_eid_od_oid
ondbo.orders(EmployeeID,OrderDate,OrderID);
createuniqueindexidx_eid_od_rd_oid
ondbo.orders(EmployeeID,OrderDate,RequiredDate,OrderID);
方法一
selectOrderID,CustomerID,EmployeeID,OrderDate,RequiredDate
fromdbo.Ordersaso1
whereOrderDate=
(selectMAX(OrderDate)
fromdbo.Ordersaso2
whereo2.EmployeeID=o1.EmployeeID)
andOrderID=
(selectMAX(OrderID)
fromdbo.Ordersaso2
whereo2.EmployeeID=o1.EmployeeID
ando2.OrderID=o1.OrderID);
方法二
selectOrderID,CustomerID,EmployeeID,OrderDate,RequiredDate
fromdbo.Ordersaso1
whereOrderID=
(selectMAX(OrderID)
fromdbo.Ordersaso2
whereo2.EmployeeID=o1.EmployeeID
andOrderDate=
(selectMAX(OrderDate)
fromdbo.orderso3
whereo3.EmployeeID=o2.EmployeeID));
方法二比方法一的性能略高，但是可读性差。
上述查询的索引准则是在（分组列，排序列，附加属性列）上创建索引
方案二，基于聚合
selectEmployeeID
,cast(SUBSTRING(binstr,1,8)asdatetime)
,CAST(substring(binstr,9,4)asint)
,CAST(substring(binstr,13,10)asNCHAR(10))
,CAST(substring(binstr,23,8)asdatetime)
from (selectEmployeeID,
max(CAST(OrderDateasBINARY(8))--binary(n) n (1,8000) 字节数
+CAST(OrderIDasBINARY(4))
+CAST(CustomerIDasBINARY(10))
+CAST(RequiredDateasbinary(8)))asbinstr
fromdbo.orders
groupbyEmployeeID)d
注意：数组转换为二进制时，只有非负值保持原有顺序。
优点：无论是否有合适的索引，他只扫描一次数据，如果有索引，可能执行有序索引扫描和基于排序的聚合，如果没有，可能执行基于哈希的聚合。
当排序列和附加属性的排序方向相反时。比如，附加属性是min（orderid），可以使用maxint-max（）实现。
selectEmployeeID
,cast(SUBSTRING(binstr,1,8)asdatetime)
,CAST(substring(binstr,9,4)asint)
,CAST(substring(binstr,13,10)asNCHAR(10))
,CAST(substring(binstr,23,8)asdatetime)
from (selectEmployeeID,
max(CAST(OrderDateasBINARY(8))--binary(n) n (1,8000) 字节数
+CAST(2147483647-OrderIDasBINARY(4))
+CAST(CustomerIDasBINARY(10))
+CAST(RequiredDateasbinary(8)))asbinstr
fromdbo.orders
groupbyEmployeeID)d
方案三基于top 添加主键作为附加属性，保证top的确定性。
ifexists(select*fromsys.indexeswherename=N'idx_eid_od_i_cid_rd'andobject_ID=object_id('dbo.Orders'))--在目录视图中查找
dropindexdbo.Orders.idx_eid_od_i_cid_rd
createuniqueindexidx_eid_od_i_cid_rd--索引名称包括索引列的缩写
ondbo.Orders(EmployeeID,OrderDate,OrderID)
include(CustomerID,RequiredDate);
ifexists(select*fromdbo.sysindexeswherename=N'idx_oid_qtyd_pid'andid=OBJECT_ID(N'dbo.[Order Details]'))--在兼容视图中查找
dropindexdbo.[Order Details].idx_oid_qtyd_pid
createuniqueindexidx_oid_qtyd_pid
ondbo.[Order Details](OrderID,Quantitydesc,ProductID)
selectorderID,CustomerID,EmployeeID,OrderDate,RequiredDate
fromdbo.Ordersaso1
whereOrderID=
(selecttop(1)OrderID
fromdbo.OrdersasO2
whereO2.EmployeeID=o1.EmployeeID
orderbyOrderDatedesc,OrderIDdesc);
优点：比方案一要快，特别是有多个排序字段（附加属性）时，只需要在order by 之后增加额外的列即可，比方案二要要慢，但是简单。
优点二：可以通过in来扩展
declare@nint
set@n= 2
selectorderID,CustomerID,EmployeeID,OrderDate,RequiredDate
fromdbo.Ordersaso1
whereOrderIDin
(selecttop(@n)OrderID
fromdbo.OrdersasO2
whereO2.EmployeeID=o1.EmployeeID
orderbyOrderDatedesc,OrderIDdesc);
--查看索引深度
selectINDEXPROPERTY(OBJECT_ID('dbo.orders'),'idx_eid_od_i_cid_rd','indexdepth');
优化提示：上述查询是对每一个订单进行一次书页查找，但实际上只需要对每一个员工进行一次索引查找。
selectorderID,CustomerID,o.EmployeeID,OrderDate,RequiredDate
from (
selectdistinctEmployeeID,toporder=(--distinct
selecttop 1 OrderIDfromdbo.Orderso2whereo2.EmployeeID=o1.EmployeeIDorderbyOrderDatedesc,OrderIDdesc)
fromdbo.Orderso1)eo
innerjoindbo.Ordersoono.OrderID=eo.toporderorderby 1;
使用row_number优化
;witha
as
(
selectorderID,CustomerID,EmployeeID,OrderDate,RequiredDate
,row_number()over(partitionbyEmployeeIDorderbyOrderDatedesc,OrderIddesc)asRowNum
fromdbo.Orders
)
select*fromawhereRowNum= 1 orderby 1
（疑问：逻辑读，预读，书页查找，索引深度？逻辑读每次读取多少页，偏移量怎么算，预读的依据是什么？书页查找怎么会发声三次逻辑读？索引深度包括root吗？）
EXISTS 和 IN，等输入列表中包含NULL时，IN实际上会产生一个UNKNOWN的逻辑结果，In （b 、c、NULL）的结果是UNNOWN，在筛选器中UNKOWN与FALSE的处理方式类似，所以in和exist的查询结果产生相同的执行计划。
Not exists 和not in
列包含null时，not in 查询总是返回一个空集，因为谓词val in （val1，val2，……，null）永远不会返回false，而是返回true或者unknown，所以Val not in(val1、val2、……、null)只会返回not true或者not unknown，不返回true
最小缺失值（Missing Value）
use test
go
if OBJECT_ID('dbo.t1') is not null
drop table dbo.t1;
go
create table t1
(
keycol int not null primary key check(keycol>0),
datacol varchar(10) not null
);
insert into t1 values(3,'a');
insert into t1 values(4,'b');
insert into t1 values(6,'c');
insert into t1 values(7,'d');
insert into t1 values(1,'d');
select
case
when not exists( select * from dbo.t1 where keycol = 1 ) then 1
else (select min(keycol+1) from t1 a where not exists( select * from t1 b where b.keycol = a.keycol + 1))
end ;
逆反逻辑(Reverse Logic)在关系分区问题中的应用
谜题：有两个守卫守在两扇门前。一扇门通向黄金和财宝，另一扇通向死亡，但是你不知道哪个是那个。一个门卫总是说真话，另一个总是说假话，但是你分不清谁说谎谁谁诚实。你会怎么问？
答案：你应该问其中一个守卫，“如果我问另一个门卫哪扇门通向黄金，他会指向哪扇门？”
Eg：返回其订单由所有的usa员工处理的消费者=返回没有订单是由非USA员工处理的消费者（双重否定）
行为不当（Misbehaving）的子查询
注意：一个好的实践是在子查询中总是为所有属性限制表名称或别名，即使子查询是独立子查询也应该如此。
不常用的谓词
Any，some，all
表表达式table expression：用作表的子查询（内联表表达式 inline table expression它包括派生表drived tables 和公用表达式 CTE）
派生表是一种从查询表达式派生出虚拟结果表的表表达式。派生表与其他表一样出现在查询的from子句中。派生表仅存在外部查询中。所以优化器不会为他生成独立的计划，编译时，外部查询和内部查询被合并，并生成一个计划。使用派生表既不会降低性能，也不会提高性能，它更多的是为了代码的简化和清晰。
派生表必须是一个有效的表。因此，它必须遵守几条规则：
所有列必须有名称
列名称必须是惟一的
不允许使用order by （除非也指定了top）
不同于标量和多值子查询，派生表不能是相关的；它必须是独立的。但当使用apply运算符时是一个例外。
应用场景：使用列别名（内联列别名，外联列别名）
select OrderYear,COUNT(distinct CostomerID) as NumCusts
from ( select YEAR(OrderDate) as OrderYear,CustomerID from dbo.Orders ) as d
group by OrderYear
派生表可以使用参数，可以嵌套。
CTE
Cte仅存在于外部查询中，对于同一批处理中的其他语句视而不见。With前面必须有；
可以使用参数，多CTE，多引用
Eg：使用cte删除重复的行
use Northwind
go
if OBJECT_ID('dbo.CustomersDups') is not null
drop table dbo.Customersdups
go
;with CrossCustomers as
(
select 1 as c,c1.*
from dbo.Customers as c1,dbo.Customers as c2
)
select ROW_NUMBER() over(order by c) as keyCol,
customerID,CompanyName,ContactName,ContactTitle,Address,
city,Region,PostalCode,country,Phone,Fax
into dbo.CustomersDups
from CrossCustomers
;with JustDups as
(
select * from CustomersDups a
where keyCol <(select MAX(keycol) from CustomersDups b where a.customerID = b.customerID)
)
delete from JustDups;
select * from CustomersDups
cte可以用在诸如视图或者内联udf这样的容器对象中，这种能力允许实现封装，这对于模块化开发是非常重要的。而且，cte不能被嵌套，但是通过容器对象封装cte并在外部cte中查询容器对象，就可以间接嵌套cte
create view dbo.VYearCnt
as
with yearCnt as
(
select year(OrderDate) as OrderYear,
count(distinct CustomerId) as NumCusts
from dbo.Orders
group by year(OrderDate)
)
select * from yearCnt;
go
select * from vyearcnt
drop view vyearcnt
create function dbo.fn_EmpYearCnt(@EmpID as int) returns table
as
return
with EmpYearCnt as
(
select year(OrderDate) as OrderYear,
count(distinct CustomerID) as NumCusts
from dbo.Orders
where EmployeeID = @EmpID
group by year(OrderDate)
)
select * from EmpYearCnt;
select * from dbo.fn_EmpYearCnt(3)
drop function fn_EmpYearCnt
cte递归
;with EmpsCTE as
(
select EmployeeID,ReportsTo,FirstName,LastName from dbo.Employees where EmployeeID = 2
union all
select a.EmployeeID,a.ReportsTo,a.FirstName,a.LastName from dbo.Employees a inner join EmpsCTE b on a.ReportsTo = b.EmployeeID
)
select * from EmpsCTE
option (maxrecursion 2)—疑问，递归会不会出现重复行