Although the calculations of other ranking functions are technically similar to those of Row_number, their actual application is much less. Rank and Dense--rank are mainly used for rankings and integrals. Ntile more for analysis.
--ranking function
/**/
The--sql Server 2005 ranking function can be used only in the SELECT and ORDER BY clauses of a query. The best index for ranking calculations, regardless of the method you use, is the index created on the partition, row, and overlay columns.
--line number: A sequential integer that is assigned to a row in a query result set in the specified order. In a later section, you will describe the tools and methods for calculating line numbers in SQL Server 2005 and earlier versions.
SELECT Empid,qty,row_number () Over (qty) as RowNum
from [Sales]
Order BY [Qty]
--Certainty
SELECT Empid,qty,row_number () Over (Order by qty) as Rownum,row_number () over (order by Qty,empid) as RowNum2
from [Sales]
ORDER BY Qty,empid
--Partition
SELECT Mgrid,empid,qty,row_number () over (PARTITION by Mgrid Order by Qty,empid) as RowNum
from [Sales]
ORDER BY Mgrid,qty
--===== the previous 2000 versions are based on a set-way implementation
--Unique row sequence: given a unique partition + sequence combination (the only partition for the following example is Empid, row sequence Empid
Select Empid (select COUNT (*) from [Sales] as S2 WHERE s2.empid<=s1.empid) as RowNum
From [Sales] S1 order by [Empid]
--View the execution plan (in order from top to bottom, from right to left) to see that there are two different operators using the clustered index. The first is a full scan to return all rows (this example is 11 rows); The second operation Fuxian for each external lookup, then performs a partial scan to complete the statistics. Do you remember? The main factors that affect the performance of data processing queries are generally I/O. This approach is not obvious in small amounts of data, but when the volume of data is larger (greater than thousands), because each record needs to scan all the tables, the total number of rows scanned using this method will be 1+2+3+n, and you will scan 50005000 rows for a 100000-row table as a whole. Incidentally, the formula for calculating the first N positive integers is (n+n squared)/2.
--see what the example means.
Use [AdventureWorks]
SET STATISTICS time on
SELECT Salesorderid,row_number () over (SalesOrderID) as RowNum
From sales. [SalesOrderHeader]
Select SalesOrderID, (select COUNT (*) from sales.[ SalesOrderHeader] B WHERE b.salesorderid<=a.salesorderid) as RowNum
From sales. [SalesOrderHeader] A
Order BY [SalesOrderID]
/* Results:
(31465 rows affected)
SQL Server Execution Time:
CPU time = 47 milliseconds, elapsed time = 674 milliseconds.
(31465 rows affected)
SQL Server Execution Time:
CPU time = 133094 milliseconds, elapsed time = 134030 milliseconds.
It is conceivable that the new ranking function of the worry is very good.
*/
--not unique row sequences and attached properties: When sorting columns is not unique, you can make it unique by introducing an additional property. The following query generates line numbers in the Order of qty and Empid
Select Empid,qty (select COUNT (*) from [Sales] s2 WHERE s2.qty<s1.qty OR ((S2.qty=s1.qty and S2.empid<=s1.empid)) S rownum
From [Sales] S1 order by Qty,empid
--The above example qty is a row sequence, Empid is an additional column. To count rows with the same or smaller sorted list value (QTY+EMPID), use the following expression in a subquery
--inner_qty < Outer_qty OR (Inner_qty=outer_qty and Inner_empid <= outer_empid)
--there is no unique sequence of attached properties: When you want to assign line numbers based on a unique row number and do not use additional properties, it is more complex to use a collection-based approach to solve the problem in a version prior to SQL Server 2005. Create and populate the table with the following code list.
IF object_id (' T1 ') is not NULL
DROP TABLE T1
CREATE TABLE T1 (col1 VARCHAR (5))
INSERT into T1 (col1) VALUES (' A ')
INSERT into T1 (col1) VALUES (' A ')
INSERT into T1 (col1) VALUES (' A ')
INSERT into T1 (col1) VALUES (' B ')
INSERT into T1 (col1) VALUES (' B ')
INSERT into T1 (col1) VALUES (' C ')
INSERT into T1 (col1) VALUES (' C ')
INSERT into T1 (col1) VALUES (' C ')
INSERT into T1 (col1) VALUES (' C ')
INSERT into T1 (col1) VALUES (' C ')
-This solution must be compatible with SQL Server 2000, so you cannot use the Row_number function. Furthermore, this scheme must be standard.
In this solution, you will use a very important key technology for the first time--generating replicas with digital secondary tables. The following creates the Nums table and populates the table with 1 million integers within l<=n<=1000000.
--The first step is to "compress" the data by pressing Col to group the rows, return the number of duplicates (the number of rows in the group) for each group, and use the subquery to return the number of rows in the base table with the lowest sorted value.
Select Col1,count (*) as Dups, (select COUNT (*) from [t1]b WHERE b.col1<a.col1) as smaller to [T1]a GROUP by [col1]
-The next step is to extend the number of rows, that is, to create consecutive numbered copies for each row.
SELECT Col1,dups,smaller,n from (
Select Col1,count (*) as Dups, (select COUNT (*) from [t1]b WHERE b.col1<a.col1) as smaller to [T1]a GROUP by [col1]) as D, Nums
WHERE N<=[dups]
--Observe the results of the above table and understand how it produces line numbers.
--The line number can be expressed as the number of rows with a smaller sort value plus the line number within the same sorted value group, that is, N + smaller. The final solution is listed below.
SELECT N+smaller as RowNum, col1 from (
Select Col1,count (*) as Dups, (select COUNT (*) from [t1]b WHERE b.col1<a.col1) as smaller to [T1]a GROUP by [col1]) as D, Nums
WHERE N<=[dups]
Order BY [RowNum]
--Create a nums table filled with a 100W row number
Go
IF object_id (' dbo. Nums ') is not NULL
DROP TABLE dbo. Nums;
Go
CREATE TABLE dbo. Nums (n INT not NULL PRIMARY KEY);
DECLARE @max as int, @rc as int;
SET @max = 1000000;
SET @rc = 1;
INSERT into Nums VALUES (1);
While @rc * 2 <= @max
BEGIN
INSERT into dbo. Nums SELECT n + @rc from dbo. Nums;
SET @rc = @rc * 2;
End
INSERT into dbo. Nums
SELECT n + @rc from dbo. Nums WHERE n + @rc <= @max;
Go
--useful in a production environment, for example, that can be used in data paging.
CREATE PROC usp_getpage @iRowCount int, @iPageNo int
As
SELECT * FROM (
SELECT row_number () over (order by ProductID ASC) rownum,* from Production.Product) OrderData
WHERE rownum BETWEEN @iRowCount * (@iPageNo-1) +1 and @iRowCount * @iPageNo
Order BY [ProductID] ASC
Go
--Using
EXEC Usp_getpage 10,20
