图片 2

根据多条件查询临时表 想得到不同结果集的方法

当我写下如下sql语句时,我得到了输入@c参数时想得到的结果集。select *
from @tb t where t.id in (select id from tb where f = @c)
但如果有@a,@b,@c,而它们分别想从@tb中得到不同的结果集,例如 复制代码 代码如下: if @a is not null begin
–得到@a想得到的 end if @b is not null begin –得到@b想得到的 end if @c
is not null begin –得到@c想得到的 end
这样做好像没什么问题,但如果@a和@b是一起的,甚至是@a,@b,@c,@d,@e,@f等等N多种条件组合,这样就不好办了。所以必须先build好@tb,最后一次性查询
–构造@tb select * from @tb
假如我已经通过@a,@b得到了一种@tb结果集,当我再次使用@c进行条件判断时,这样就会覆盖刚才的结果。
可以采用“删除不符合条件的记录”的方法来做,由于@tb已经得到了@a,@b想得到的结果,所以只要删除掉不符合@c的结果就行了。完。
复制代码 代码如下: if @c is not null
begin delete c from @tb c where c.id not in (select id from tb where f =
@c) end select * from @tb

然而,微软sql
server在处理这类索引时,有个重要的缺陷,那就是把本该编译成索引seek的操作编成了索引扫描,这可能导致严重性能下降
举个例子来说明问题,假设某个表T有索引 ( cityid, sentdate, userid),
现在有个分页列表功能,要获得大于某个多列复合索引V0的若干个记录的查询,用最简单表意的方式写出来就是
V = V0, 如果分解开来,就是: cityid @cityid0 or (cityid = @cityid0 and
(sentdate @sentdate0 or (sentdate = @sentdate0 and userid = @userid0))),
当你写出上述查询时,你会期待sql server会自动的把上述识别为V =
V0类型的边界条件,并使用index seek操作来实施该查询。然而,微软的sql
server (2005版)有一个重要缺陷(其他的sql server如何还不得知),
当它遇到这样sql时,sql server就会采用index
scan来实施,结果是您建立好的索引根本就没有被使用,如果这个表的数据量很大,那所造成的性能下降是非常大的。
对于这个问题,我曾经提交给微软的有关人士,他们进一步要求我去一个正式的网站上去提交这个缺陷,我懒得去做。
不过,对这个缺陷,还是有个办法能够绕过去的,只要把上面给出的条件变变形,sql
server还是能够变回到是用index seek, 而不是低性能的index scan.
具体请看我的英文原文吧(对不起了,
我一旦写了中文,就不想翻成英文,反过来也一样,
估计大家英文都还可以,实在不行的就看黑体部分吧, ): The seek predicate of
the form “x bookmark_of_x” is needed in paging related query. The
compiler has no difficulty to parse it correctly if x is a single column
index, or two columns index, however, if x is a three columns index or
more, then the compiler will have a hard time to recognize it. This
failure will result in that the seek predicate ended up in residue
predicate, which results in a much worse execution plan. To illustrate
the point, take a example, Create table A( a int, b int, c int, d float,
primary key (a, b, c)) now check the plan for the query: select c, d
from A where (a 111 or a= 111 and (b 222 or b = 222 and c 333)) you can
see a table scan op is used, and the Where clause ended up in residue
predicate. However, if you rewrite the query in an equivalent form:
select c, d from A where a 111 or a= 111 and b 222 or a= 111 and b= 222
and c 333 Then the compiler can choose an index seek op, which is
desired. The problem is, the compiler should be able to recognize the
first form of seek predicate on multiple columns index, it saves the
user from having to pay extra time to figure out a get-around, not to
mention the first form is a more efficient form of same expression.
上面的问题,可以说是部分的绕过去了,但是,也有绕不过的时候,接着看下面一段:
It looks like that sql server lacks a consept of vector bookmark, or
vector comparison or whatever you like to call it. The workaround is not
a perfect workaround. If sql server were to understand the concept of
vector bookmark, then the following two would be the same in execution
plan and performance: 1. select top(n) * from A where vectorIndex =
@vectorIndex 2. select * from A where vectorIndex = @vectorIndex and
vectorIndex =@vectorIndexEnd — @vectorIndexEnd corresponds to the last
row of 1. However, test has shown that, the second statement takes far
more time than the first statement, and sql server actually only seek to
the begining of the vector range and scan to the end of the whole Index,
instead of stop at the end of the vector range. Not only sql server
compile badly when the vector bookmark has 3 columns, test has shown
that even with as few as 2 columns, sql serer still can not correctly
recognize this is actually a vector range, example: 3. select top (100)
a, b, c, d from A where a 60 or a= 60 and b 20 4. select a, b, c, d from
A where (a 60 or a= 60 and b 20) and (a 60 or a= 60 and b = 21),
上面两个查询实质相同(表中的数据刚好如此),并且给出同业的结果集,但是,3比4的速度要快的多,如果去看execution
plan也证明3确实应当比4快. 也就是说,
即使在索引vectorIndex只含两列的情况下, sql
server也无法正确的理解范围表达式 @vectorIndex0 vectorIndex
@vectorIndex1, 它能把前半部分正确的解读为seek, 但是,
后半部分无法正确解读, 导致, sql server会一直扫描到整个表的末尾,
而不是在@vectorIndex1处停下来. 以下测试代码, 有兴趣的人可以拿去自己玩:
复制代码 代码如下: CREATE TABLE
[dbo].[A]( [a] [int] NOT NULL, [b] [int] NOT NULL, [c]
[int] NOT NULL, [d] [float] NULL, PRIMARY KEY CLUSTERED ([a]
ASC, [b] ASC, [c] ASC) ) declare @a int, @b int, @c int set @a =1
while @a = 100 begin set @b = 1 begin tran while @b = 100 begin set @c =
1 while @c = 100 begin INSERT INTO A (a, b, c, d) VALUES
(@a,@b,@c,@a+@b+@c) set @c = @c + 1 end set @b = @b + 1 end commit set
@a = @a + 1 end SET STATISTICS PROFILE ON SET STATISTICS time ON SET
STATISTICS io ON select top (10) a, b, c, d from A where (a 60 or a= 60
and (b 20 or b = 20 and c = 31)) select a, b, c, d from A where (a 60 or
a= 60 and (b 20 or b = 20 and c = 31)) and (a 60 or a= 60 and (b 20 or b
= 20 and c = 40)) select top (10) a, b, c, d from A where a 60 or a= 60
and b 20 or a= 60 and b= 20 and c = 31 select a, b, c, d from A where (a
60 or a= 60 and b 20 or a= 60 and b= 20 and c = 31) and (a 60 or a= 60
and b 20 or a= 60 and b= 20 and c = 40) select top (100) a, b, c, d from
A where a 60 or a= 60 and b 20 select a, b, c, d from A where (a 60 or
a= 60 and b 20) and (a 60 or a= 60 and b = 21) select top (100) a, b, c,
d from A where a 60 or a= 60 and b 20 select a, b, c, d from A where (a
60 or a= 60 and b 20) and (a 60 or a= 60 and b = 21)

if @a>@b
begin
set @c = @a + @b;
end
else
begin
set @c = @a – @b;
end

视图就是我们查询出来的虚拟表
创建视图:create view 视图名
as
SQL查询语句,分组,排序,in 等都不能写
视图的用法: select * from 视图名

SQL编程

if … else 的用法,if后面没有小括号,花括号用begin end 替代

图片 1

视图

定义变量:declare @变量名 数据类型 declare @a int
变量赋值:set @变量名 = 值 set @a=10

图片 2