详见博客地址:http://blog.csdn.net/feixianxxx/archive/2010/03/21/5402391.aspx
-
SQL code
-
/* ---------------------------------------------------------------------- *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是相等的,不同于一般的筛选器.
-
SQL code
-
-- 环境 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 高很多
-
SQL code
-
-- 环境 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.利用字符串解决带附加属性的问题
注意点:当需要附加属性来解决问题时候,一般会想到子查询,但是因为子查询需要良好的索引设计,所以不太好用.可以使用字符串来解决这个问题
-
SQL code
-
-- 环境 create table test_4 (id int , a int , b int , c int ) insert test_4 select 1 , 2 , 3 , 4 union all select 1 , 3 , 5 , 4 union all select 1 , 3 , 7 , 4 union all select 1 , 3 , 7 , 8 union all select 2 , 2 , 3 , 4 union all select 2 , 5 , 3 , 8 union all select 2 , 5 , 3 , 8 union all select 2 , 7 , 3 , 8 union all select 2 , 1 , 9 , 9 go -- 字符串 select ID, a = SUBSTRING (COL, 1 , 5 ), b = SUBSTRING (COL, 6 , 5 ), c = SUBSTRING (COL, 11 , 5 ) from ( select ID, MAX ( CAST (a as char ( 5 )) + CAST (b as char ( 5 )) + CAST (c as char ( 5 ))) as col from test_4 group by ID ) l -- 子查询 select * from test_4 k where not exists ( select * from test_4 where id = k.id and (a > k.a or a = k.a and b > k.b or a = k.a and b = k.b and c > k.c)) /* ID a b c ----------- ---------- ---------- ---------- 1 3 7 8 2 7 3 8 */
这个字符串优点是无论是否有好的索引,因为它只扫描一次(这里数据量太小)
--4.利用聚合实现字符串拼接
注意:不用XML、函数、临时表、游标去实现字符串的拼接
-
SQL code
-
-- 环境 create table test_5(empid int , name varchar ( 10 )) insert test_5 select 1 , ' a ' union all select 1 , ' b ' union all select 1 , ' c ' union all select 1 , ' d ' union all select 2 , ' a ' union all select 2 , ' t ' union all select 2 , ' v ' select empid, name = MAX ( case when rn = 1 then name else '' end ) + MAX ( case when rn = 2 then ' , ' + name else '' end ) + MAX ( case when rn = 3 then ' , ' + name else '' end ) + MAX ( case when rn = 4 then ' , ' + name else '' end ) from ( select empid,name, ( select COUNT ( * ) from test_5 where k.empid = empid and k.name >= name) as rn from test_5 k )z group by empid -- xml select empid, name = stuff (( select ' , ' + name as [ text() ] from test_5 where k.empid = empid order by name for XML PATH( '' )), 1 , 1 , '' ) from test_5 k group by empid /* empid name ----------- ------------------------------------------- 1 a,b,c,d 2 a,t,v */
ps:该方法前提是name不能在同一组里出现重复,且每组内最大记录数不是很大。通过执行发现该方法效率优于XML
适合用于SQL2000中想实现字符串拼接但是又不想函数的情况下.
5.TOP + ORDER BY 求中值
注意点:可以利用TOP + ORDER BY 巧妙解决取中值的问题,而且在SQL2000也可以方便使用
-
SQL code
-
-- 环境 create table test_6(rq varchar ( 8 ), ddsj int ) insert into test_6 select ' 200805 ' , 30 union all select ' 200805 ' , 40 union all select ' 200805 ' , 50 union all select ' 200805 ' , 20 union all select ' 200806 ' , 250 union all select ' 200806 ' , 200 union all select ' 200806 ' , 310 union all select ' 200806 ' , 100 union all select ' 200806 ' , 130 go -- TOP + ORDER BY 求中值(NTILE同样的效果) select rq, ( ( select MAX (ddsj) as ddsj from ( select top 50 percent ddsj from test_6 where rq = k.rq order by ddsj ) k ) + ( select MIN (ddsj) as ddsj from ( select top 50 percent ddsj from test_6 where rq = k.rq order by ddsj desc ) k ) ) / 2 as ddsj from test_6 k group by rq -- 利用位置的收尾呼应~ ; with cte as ( select * ,ROW_NUMBER() over (partition by rq order by ddsj ) as rn1, ROW_NUMBER() over (partition by rq order by ddsj desc ) as rn2 from test_6 ) select rq,ddsj = AVG (ddsj) from cte where abs (rn1 - rn2) <= 1 group by rq /* rq ddsj -------- ----------- 200805 35 200806 200 */
在适用 缺点是没有合适的索引将会很慢
--6 OR 转变成AND
注意点:大家都知道OR在where条件出现时候,一般来说意味着索引的失效,只要筛选列上有一个没有索引的话.相比较而言,AND是有个索引就有个效果.所以我们有必要在能将OR转成AND的时候就转化.
-
SQL code
-
-- 环境 create table test_7 ( id int , col1 int , col2 int ) create index in_col1 on test_7(col1) insert test_7 values ( 1 , 1 , 3 ) insert test_7 values ( 1 , 1 , 2 ) insert test_7 values ( 1 , 2 , 3 ) go 100000 insert test_7 values ( 1 , 3 , 3 ) insert test_7 values ( 1 , 3 , 1 ) insert test_7 values ( 2 , 2 , 3 ) insert test_7 values ( 2 , 2 , 5 ) insert test_7 values ( 2 , 4 , 9 ) go -- or select * from test_7 where col1 < 1 or (col1 = 1 and col2 < 3 ) -- and select * from test_7 where col1 <= 1 and (col1 < 1 or col2 < 3 )
通过执行计划可以看到 带AND的开销比OR少了倍,它用到了COL1上的索引
--7 利用计算表达式改变IDENTITY
注意:使用select into 表的时候如果有自增列 会把自增的属性也复制过去,可以通过计算表达式去去除这个自增属性
-
SQL code
-
-- 环境 create table test_8(id int identity ( 1 , 1 ),a int ) insert test_8 select 1 insert test_8 select 2 insert test_8 select 3 insert test_8 select 7 insert test_8 select 9 go select ID + 0 as id ,A into # 1 from test_8 insert # 1 (a) select 8 select * from # 1 /* id A ----------- ----------- NULL 8 ---没有自增 1 1 2 2 3 3 4 7 5 9 */
--8.set 中的连等
注意:在更新时也可以使用连续等于 @i=col=@i+1 <===> @i加后赋值给COL字段
-
SQL code
-
-- 环境 CREATE TABLE test_9 ( id INT NOT NULL , col VARCHAR ( 5 ) NOT NULL ); INSERT INTO test_9 VALUES ( 0 , ' A ' ); INSERT INTO test_9 VALUES ( 0 , ' B ' ); INSERT INTO test_9 VALUES ( 0 , ' C ' ); INSERT INTO test_9 VALUES ( 0 , ' C ' ); INSERT INTO test_9 VALUES ( 0 , ' C ' ); INSERT INTO test_9 VALUES ( 0 , ' B ' ); INSERT INTO test_9 VALUES ( 0 , ' A ' ); go DECLARE @i AS INT ; SET @i = 0 ; UPDATE test_9 SET @i = id = @i + 1 ; go select * from test_9 /* id col ----------- ----- 1 A 2 B 3 C 4 C 5 C 6 B 7 A */
扫一扫
544
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
