排序函数可以用来协助给结果集增加位置信息。SQL SERVER 2008只有四个排序函数,介绍如下: ROW_NUMBER:返回结果集中给定行的序号 示例如下: CREATE VIEW contractSubset AS SELECT TOP 20 * FROM Person.Person WHERE FirstName like 'b%' select firstname, (select COUNT(*) from contractSubset as c where c.FirstName <contractSubset.FirstName)+1 As Rank from contractSubset order by FirstName SELECT firstname, ROW_NUMBER() OVER (ORDER BY firstname) AS 'ROW_NUMBER', RANK() OVER (ORDER BY firstname) as 'RANK', DENSE_RANK() OVER (ORDER BY firstname) as 'DENSE_RANK', NTILE(4) OVER (ORDER BY firstname) AS 'NTILE(4)' from contractSubset order by FirstName
|
|
来自: 小鱼儿363 > 《sqlserver优化》