【SQL中一些特别地方特别解法】,该怎么解决-MSSQL教程-爱易网页

【SQL中一些特别地方特别解法】,该怎么解决

日期：2014-05-18　浏览次数：20495 次

【SQL中一些特别地方特别解法】
详见博客地址: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 al

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

【SQL中一些特别地方特别解法】,该怎么解决

相关资料更多>

推荐阅读更多>