Sql Server2005 TransactSQL 新兵器学习总结之TOP 运算符

 

TOP 运算符介绍：
TOP 运算符在Sql Server2005得到了增强，可以使用任何数值表达式（如变量名），而不是仅使用整数来指定该运算符返回的行数。
TOP 现在还可以在 INSERT、UPDATE 和 DELETE 语句中指定。

TOP 运算符的几个使用总结：
1．TOP 表达式可用在 SELECT、INSERT、UPDATE 和 DELETE 语句中。
2．Top表达式可以是常量，变量，子查询
3．取代set rowcount，可以防止锁升级，提高效率


特别提示:
与INSERT、UPDATE 或 DELETE 一起使用的 TOP 表达式中被引用行将不按任何顺序排列。
TOP n 随机返回 n 行。
例如，下面的 INSERT 语句包含 ORDER BY 子句，但该子句并不影响由 INSERT 语句直接引用的行。

请看例题：
有1个test表，表结构和表中数据如下：
select * from test

id salary manid
100 6500 100
200 5500 200
101 6600 100
102 6200 100
103 5100 100
104 6700 100
201 5800 200
202 4200 200

执行下面的语句：

declare @t table(id int,salary int,manid int)

INSERT TOP (2) INTO @t 
SELECT  id, salary, manid
from test
ORDER BY salary  asc

select * from @t

结果如下：
id salary manid
100 6500 100
200 5500 200

上个查询中的 ORDER BY 子句仅引用嵌套 SELECT 语句返回的行。
INSERT 语句选择 SELECT 语句返回的任意两行。
若要确保插入 SELECT 子查询返回的前两行，可以按如下写该查询。

declare @t table(id int,salary int,manid int)

INSERT  INTO @t 
SELECT TOP (2) id, salary, manid
from test
ORDER BY salary  asc

select * from @t

结果如下：
id salary manid
202 4200 200
103 5100 100

TOP 运算符在Sql Server2005得到了增强,Top表达式可以是常量，变量，子查询
下面我们再来看另个例题：

create table test2( 
[no] int ,
n nvarchar(100)
)

insert into test2([no],n)
select 1,'a'
union 
select 2,'b'
union 
select 3,'c'
union 
select 3,'c2'
union 
select 2,'b2'
union
select 2,'b3'
go
select * 
from test2 
order by [no] asc
go

结果如下：
no n
1 a
2 b
2 b2
2 b3
3 c
3 c2

1.利用top变量,筛选no最小的2行

-- 利用top变量,筛选no最小的2行
declare @i int
select @i = 2
select top (@i) * 
from test2 
order by [no] asc
go

结果如下：
no n
1 a
2 b

我们可以看到,在Sql Server2005中top后面跟的可以是变量了,而在Sql Server2000中必须是常量才可以
要在Sql Server2000实现上述功能比较麻烦，需要用到动态sql语句，请看下面代码：

--Sql Server2000版本
declare @i int
select @i = 2

declare @sql nvarchar(255)
select @sql=''

select @sql='select top ('+ convert(nvarchar(100),@i)+') * from test2 order by [no] asc'
exec(@sql)

2.利用top子查询来筛选

select top ( select count([no]) from test2 where [no]=3 ) * 
from test2 
order by [no] asc
go

结果如下：
no n
1 a
2 b

3.WITH TIES参数：
指定从基本结果集中返回更多的行，返回的行与TOP n (PERCENT) 行中的最后一行在ORDER BY 列中具有相同的值。
只有在指定ORDER BY 子句之后才能指定TOP WITH TIES。

--返回no前2行，并还返回no=2的行
select top (2) WITH TIES * 
from test2 
order by [no] asc
go