sum(d.tax)
from invoice_detail d,
invoice_extractor e
where (e.pga_status = 0
or e.rd_status = 0)
and suitable_join_condition
and (d.type_code in (3, 7, 2)
or (d.type_code = 4
and d.subtype_code not in
(select trans_code
from trans_description
where trans_category in (6, 7))))
group by what_is_required
having sum(d.tax) != 0
最后一个条件有问题(它使我想起了《绿野仙踪》里的黄砖路,甚至使我做起了“负税率”的白
日梦):
sum(d.tax) != 0
如前所述,换成下列条件更加合理:
and d.tax > 0
上述的例子中,使用集合cāo作符会相当笨拙,因为必须访问invoice_detail表好几次——如你所
料,那不是个轻量级的表。当然,还要看每个条件的可选择xìng,如果type_ code=4很少见,那
么它就是个可选择xìng很高的条件,exists或许会比not in ()更适合。另外,如果trans_description正
好是个小型表(或者相对较小),尝试通过单独cāo作测试存在xìng,并起不到改善xìng能的效果。
另一个表达非存在xìng的方法很有趣——而且通常相当高效——是使用外连接(outer join)。外连
接的主要目的是,返回来自一个表的所有信息及连接表中的对应信息。无对应信息的记录也需
返回——查找另一个表中无对应信息的数据时,这些记录正好是我们的兴趣所在,可通过检查
连接表的字段值是否为null找出它们。
例如:
select whatever
from invoice_detail
where type_code = 4
and subtype_code not in
(select trans_code
from trans_description
where trans_category in (6, 7))
或重写为:
select whatever
from invoice_detail
outer join trans_description
on trans_description.trans_category in (6, 7)
and trans_description.trans_code = invoice_detail.subtype_code
where trans_description.trans_code is null
我故意在join子句中加上trans_catego