详见博客地址:http://blog.csdn.net/feixianxxx/archive/2010/03/21/5402391.aspx
/*----------------------------------
*auther:Poofly
*date:2010.3.14
*VERSION:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 19 2008
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: )
*转载请注明出处
*更多精彩内容,请进http://blog.csdn.net/feixianxxx
------------------------------------*/
--收集一些东西来写写
--1.EXCEPT
注意点:2个NULL值对于EXCEPT是相等的,不同于一般的筛选器.
--环境
create table test_1 (a int ,b int)
create table test_2 (c int, d int)
insert test_1 select 1,2 union all select 1,null union all select 3,4
insert test_2 select 1,2 union all select 1,null
-- except
select * from test_1
except
select * from test_2
/*
a b
----------- -----------
3 4
*/
-- not exists
select * from test_1
where not exists(select * from test_2 where a=c and b=d)
/*
a b
----------- -----------
1 NULL --这条记录对于test_1来说是唯一的
3 4
*/
ps:因为现有版本不支持 except all 所以EXCEPT使用时候会有一个排序的阶段,效率一般不太好
--2.OVER()子句
注意点:OVER()子句在多聚合情况下比在select 下的子查询效率 or GROUP BY 高很多
--环境
create table test_3
(
id int,
value int
)
insert test_3 values(1,12)
insert test_3 values(1,1)
insert test_3 values(1,3)
insert test_3 values(1,2)
insert test_3 values(1,6)
insert test_3 values(2,1)
insert test_3 values(2,2)
insert test_3 values(2,4)
insert test_3 values(2,3)
go
--OVER
SELECT ID,
[SUM]=SUM(VALUE) OVER(),
[AVG]=AVG(VALUE) OVER(),
[COUNT]=COUNT(VALUE) OVER(),
[MAX]=MAX(VALUE) OVER()
FROM test_3
--子查询
select id,
[SUM]=(select SUM(VALUE) from test_3 where l.id=id),
[AVG]=(select AVG(VALUE) O from test_3 where l.id=id),
[COUNT]=(select COUNT(VALUE) from test_3 where l.id=id),
[MAX]=(select MAX(VALUE) from test_3 where l.id=id)
FROM test_3 l
--group by
SELECT ID,
[SUM]=SUM(VALUE),
[AVG]=AVG(VALUE) ,
[COUNT]=COUNT(VALUE) ,
[MAX]=MAX(VALUE)
FROM test_3
group by id
CTRL+L 可以发现 over的开销明显小于后者..其中子查询效率极差
--3.利用字符串解决带附加属性的问题
注意点:当需要附加属性来解决问题时候,一般会想到子查询,但是因为子查询需要良好的索引设计,所以不太好用.可以使用字符串来解决这个问题