【SQL中一些特别地方特别解法】

[feixianxxx]

详见博客地址: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.利用字符串解决带附加属性的问题
注意点:当需要附加属性来解决问题时候,一般会想到子查询,但是因为子查询需要良好的索引设计,所以不太好用.可以使用字符串来解决这个问题

--环境
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、函数、临时表、游标去实现字符串的拼接

--环境
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也可以方便使用

--环境
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的时候就转化.

--环境
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 表的时候如果有自增列 会把自增的属性也复制过去,可以通过计算表达式去去除这个自增属性

--环境
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字段

--环境
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
*/

[aNIITtoKYRJ]

搞不懂啊 ，太多啦 ，想问下一个学生管理表中有3个班学生的成绩，要把他们成绩80分以上的按班排列出来并统计每个班80分以上的人数，谢谢

[c838125301]

我是初学者，还真看不懂啊！！

[fcuandy]

[Quote=引用 88 楼 feixianxxx 的回复:]
引用 26 楼 fcuandy 的回复:
看完后不得不补充几点:
1,7,8条没什么好说的

2, 其实看查询计划可以看出2因为窗口操作，会有比较复杂的逻辑运算在里面。
子查询写法，当然是不用说的
当你开启io选项查看后，其实group by是最简单的.
当分组依赖列（或列组合）上索引后，特别是聚集索引后，group by的优势就更加体现。

3, 这个做法，实际上用了 空格占位……
[/Quote]

从逻辑上说，你改过的这个变换还是不成立的，其实第一个逻辑运算是第二个逻辑运算的子集。
那天我写了变换步聚和公式，大半页，但是说我没登陆，全没了，今天不想重敲了。

[a8888java]

[Quote=引用 26 楼 fcuandy 的回复:]
看完后不得不补充几点:
1,7,8条没什么好说的

2, 其实看查询计划可以看出2因为窗口操作，会有比较复杂的逻辑运算在里面。
子查询写法，当然是不用说的
当你开启io选项查看后，其实group by是最简单的.
当分组依赖列（或列组合）上索引后，特别是聚集索引后，group by的优势就更加体现。

3, 这个做法，实际上用了 空格占位的做法， 但是一想就知道有问题，因为空格占位……
[/Quote]

[hmx107]

谢谢分享 学习中

[masky5310]

[feixianxxx]

[Quote=引用 154 楼 fcuandy 的回复:]
引用 88 楼 feixianxxx 的回复:
引用 26 楼 fcuandy 的回复:
看完后不得不补充几点:
1,7,8条没什么好说的

2, 其实看查询计划可以看出2因为窗口操作，会有比较复杂的逻辑运算在里面。
子查询写法，当然是不用说的
当你开启io选项查看后，其实group by是最简单的.
当分组依赖列（或列组合）上索引后，特别是聚集索引后，group by的优势就更加……
[/Quote]
oh god.....
FC 那关于那个OR的逻辑转换呢

[wo554006164]

慢慢看,学习了

[wer31156]

太感谢你了

[lbh119]

收藏 !

[向往自由的普通人]

确实经典，非常不错！！！！！！！！！

[lfq881229]

O(∩_∩)O~，很不错啊，

[liaoyukun111]

写的好 特别是拼接
http://avatar.profile.csdn.net/1/B/4/2_bancxc.jpg

[lao_bulls]

谢谢，学习。

[yjp2006]

很好，，，学习。。。。

[wangjiancy]

mark

[lhblxm]

<wb
vb ai ssss
/wb>

[yangyangxie]

mark

[zhangjiang264]

回复后，慢慢看。

[老黎]

收藏.........