概念:用来处理前几条数据,在2005中已经有了很大的扩展,可以删除,更新前几条数据.也可以返回指定百分比的数据
用处:在2000中,经常用来现实分页算法.听说SQL Server对Top语句,有很好的优化.
示例一:(基本用法)
--Traditionally usage
use AdventureWorks
go
select top 10 * from Person.Contact
示例二:(使用表达式)
--Using expression
select top (datepart(mm,getdate())) * from Person.Contact
declare @top as smallint;
set @top = 10
select top (@top) * from Person.Contac
注意:表达式要用()号包起来
示例三:(使用子查询)
--Using sub query
SELECT TOP (SELECT COUNT(*) FROM Sales.SalesPerson)
SalesOrderID, RevisionNumber, OrderDate
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID
示例四:(使用百分比:PERCENT)
--Using PERCENT----
declare @Top as float, @All as bigint;
set @Top = 10;
select top (@Top) PERCENT * from Person.Contact; --return 1998 rows
select @all = count(1) from Person.Contact;
print @all; --total: 19972 rows
注意表达式必须是float类型的
示例四:(update, delete中使用top)
--Using TOP in delete
delete top(1) p
from Person.Contact as p
--order by Title --这句有错误, 好像不支持
阅读(789) | 评论(0) | 转发(0) |