_ Tom Moreau _
_ Translated by mschen _
T-SQL允许你使用不同的方法解决一个问题.有的时候,尽管选择不是那么明显,但是却可以让你得到令人满意的和快乐的惊奇.下边让我们解读Dr. __ Tom __ Moreau对同一问题不同的可能性的探索.可能我们可以在那些不同的方法之中发现一些珍贵的东西.
__ __ 让我们以我们的老朋友Northwind数据库为例,这里我们用到的是[order __ details]表,这个表是一个定单的明细表,和order表是多对一的关系.也就是一个定单对应多个订购的产品.假设你想得到每个定单订购的总价值,但是不包括59号产品.Listing __ 1给了我们第一种解法:
select
__ __ OrderID,sum __ (Quantity __ * __ UnitPrice) __ value
from
__ __ [Order __ Details] __ o1
where
__ __ ProductID __ <> __ 59
group __ by
__ __ OrderID
上边的语句很简单,它排除掉了59号产品的定单明细条目,然后进行分组统计.但是如果我们需要忽略掉订购59号产品的定单呢？也就是说我们要统计没有包含59号产品的定单的价值.你想到了WHERE, __ NOT __ EXIST(S)关键词了吗？Listing __ 2给了我们第二种方法:
select
__ __ o1.OrderID,sum __ (o1.Quantity __ * __ o1.UnitPrice) __ value
from
__ __ [Order __ Details] __ o1
where __ not __ exists
(
__ __ select
__ __ __ __ *
__ __ from
__ __ __ __ [Order __ Details] __ o2
__ __ where
__ __ __ __ o2.OrderID __ __ __ = __ o1.OrderID
__ __ and __ o2.ProductID __ = __ 59
)
group __ by
__ __ o1.OrderID
如果你不喜欢用exist的话,你可以转化成使用not __ in:
Listing __ 3
select
__ __ o1.OrderID,sum __ (o1.Quantity __ * __ o1.UnitPrice) __ value
from
__ __ [Order __ Details] __ o1
where __ 59 __ not __ in
(
__ __ select
__ __ __ __ ProductID
__ __ from
__ __ __ __ [Order __ Details] __ o2
__ __ where
__ __ __ __ o2.OrderID __ __ __ = __ o1.OrderID
)
group __ by
__ __ o1.OrderID
尽管Listing __ 1不满足我们现在的查询条件.但是从性能发面考虑,Listing __ 1还是最好的,因为它只用到了一次表的扫描.而后边的两个查询都是用到了相关子查询,如果你查看查询计划就回看到,他们都涉及到了两次表的扫描.如果你曾经在 __ T-SQL用过交叉表查询的话,你就不会对聚集函数里边的case结构陌生.现在我们就把这个非常有趣的方法应用到我们的问题中来:
Listing __ 4
select
__ __ OrderID,sum __ (Quantity __ * __ UnitPrice) __ value
from
__ __ [Order __ Details] __ o1
group __ by
__ __ OrderID
having
__ __ sum __ (case __ when __ ProductID __ = __ 59 __ then __ 1 __ else __ 0 __ end) __ = __ 0
HAVING子句起到了对分组的结果进行过滤的作用.如果没有包含59号产品,就会出现0=0,显然这是满足条件的.如果包含了59号产品的订购,就会出现n=0(n<>0),这样的定单就回被过滤掉.查看执行计划你就回发现是一次表的扫描,非常棒!
__ __ 再来举一个例子:我们这回用到的表是order表,假设我们要统计只通过一个雇员雇员下定单的顾客.你可以想到用子查询not __ exist来实现:
select __ distinct
__ __ o1.CustomerID
from
__ __ Orders __ o1
where __ not __ exists
(
__ __ select
__ __ __ __ *
__ __ from
__ __ __ __ Orders __ o2
__ __ __ __ where
__ __ __ __ __ __ o2.CustomerID __ = __ o1.CustomerID
__ __ __ __ and __ o2.EmployeeID __ <> __ o1.EmployeeID
)
同样的,这个语句可以通过带有HAVING子句的分组来实现.
Listing __ 6
select
__ __ CustomerID
from
__ __ Orders
group __ by
__ __ CustomerID
having
__ __ min __ (EmployeeID) __ = __ max __ (EmployeeID)
另一种方法:
Listing __ 7
select
CustomerID
from
__ __ Orders
group __ by
__ __ CustomerID
having
__ __ count __ (distinct __ EmployeeID) __ = __ 1
Listing __ 6和Listing __ 7查询消耗都要小于Listing __ 5.相比Listing __ 5的两次表扫描,他们只进行一次表的扫描.而Listing __ 6的损耗还要稍微小于Listing __ 7.但是,Listing __ 7的一个显著的特点就是它可以适应到一个顾客对应两个雇员,三个雇员......
其实大家可能现在明白了这篇文章将的是什么?它就是教我们怎么用having __ 子句来达到过滤组的目的.可以达到避免两次表扫描的目的.可以达到更高的性能.我从这篇文章学到了很多的方法,你呢?