Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
看了下SQL SERVER2005 关于TOP的定义,发现有一些用法还没掌握
1,取前40% select top (40) percent from table1 –若计算出的行数是小数,则最终行数会向上取整
2,declare @i int
set @i=1
select top @i, * from table1
在存储过程中得不到执行,因为top时后面只能是常量。 之前这样认为,并用了set rowcount @i解决,
但其实可用:select top (@i) * from table1来解决
3,在UPDATE,DELETE 中使用TOP
UPDATE TOP (2) frmuser
SET LastEditDate = GETDATE()
DELETE TOP (2) FROM frmUser
4,select top (@i)使用:对于sys.objects数据库,若类型为s则取2条,v 1条,p,3条
WITH lib AS(
SELECT ‘s’ TYPE ,2 showcount –将规则固定化
UNION ALL
SELECT ‘v’,1
UNION ALL
SELECT ‘p’,3
)
SELECT lib.*,o.*
FROM lib
CROSS APPLY(SELECT TOP (lib.showcount) NAME,create_date
FROM sys.objects o
WHERE o.[type] = lib.type
ORDER BY o.create_date)o
ORDER BY lib.type,o.create_Date