TOP的语法在SQL SERVER 2005中有新增加的东西了,可以支持update,delete,数学表达式等,举例如下:
建立一个表如下
Create table Mytable2 (au_id int,
authors_name varchar(100))
Go
insert into MyTable2 select 110,'Mathew Arnold'
insert into MyTable2 select 140,'Keith Davis'
insert into MyTable2 select 76,'David Taylor'
insert into MyTable2 select 127,'Agatha Christy'
insert into MyTable2 select 12,'Sidney Sheldon'
insert into MyTable2 select 12,'Mcarthur'
insert into MyTable2 select 76,'Alan Smiles'
insert into MyTable2 select 100,'Kreisler'
insert into MyTable2 select 88,'Jeff Watch'
insert into MyTable2 select 99,'William Anderson'
insert into MyTable2 select 99,'William Anderson'
go
使用UPDATE语句:
使用delete:
建立一个表如下
Create table Mytable2 (au_id int,
![](/img/return.gif)
Go
insert into MyTable2 select 110,'Mathew Arnold'
insert into MyTable2 select 140,'Keith Davis'
insert into MyTable2 select 76,'David Taylor'
insert into MyTable2 select 127,'Agatha Christy'
insert into MyTable2 select 12,'Sidney Sheldon'
insert into MyTable2 select 12,'Mcarthur'
insert into MyTable2 select 76,'Alan Smiles'
insert into MyTable2 select 100,'Kreisler'
insert into MyTable2 select 88,'Jeff Watch'
insert into MyTable2 select 99,'William Anderson'
insert into MyTable2 select 99,'William Anderson'
go
使用UPDATE语句:
Update top (2) MyTable2 set authors_name = 'Mr. ' + authors_name
select * from MyTable2
结果是:
au_id | authors_name |
110 | Mr. Mathew Arnold |
140 | Mr. Keith Davis |
76 | David Taylor |
127 | Agatha Christy |
12 | Sidney Sheldon |
12 | Mcarthur |
76 | Alan Smiles |
100 | Kreisler |
88 | Jeff Watch |
99 | William Anderson |
99 | William Anderson |
使用delete:
delete top (1) MyTable2 where left(authors_name,2)= 'Mr' select * from MyTable2
也可以设置表达式:declare @n int set @n=3 select top (@n) * from MyTable2