轉(zhuǎn)自： http://www.cnblogs.com/icebutterfly/archive/2009/08/05/1539657.html

語(yǔ)法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

簡(jiǎn)單的說(shuō)row_number()從1開始，為每一條分組記錄返回一個(gè)數(shù)字，這里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再為降序以后的沒(méi)條xlh記錄返回一個(gè)序號(hào)。
示例：
xlh?????????? row_num
1700????????????? 1
1500????????????? 2
1085????????????? 3
710??????????????? 4

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根據(jù)COL1分組，在分組內(nèi)部根據(jù) COL2排序，而此函數(shù)計(jì)算的值就表示每組內(nèi)部排序后的順序編號(hào)（組內(nèi)連續(xù)的唯一的)

實(shí)例：

初始化數(shù)據(jù)

create table employee (empid int ,deptid int ,salary decimal(10,2))
insert into employee values(1,10,5500.00)
insert into employee values(2,10,4500.00)
insert into employee values(3,20,1900.00)
insert into employee values(4,20,4800.00)
insert into employee values(5,40,6500.00)
insert into employee values(6,40,14500.00)
insert into employee values(7,40,44500.00)
insert into employee values(8,50,6500.00)
insert into employee values(9,50,7500.00)

數(shù)據(jù)顯示為

empid?????? deptid????? salary
----------- ----------- ---------------------------------------
1?????????? 10????????? 5500.00
2?????????? 10????????? 4500.00
3?????????? 20????????? 1900.00
4?????????? 20????????? 4800.00
5?????????? 40????????? 6500.00
6?????????? 40????????? 14500.00
7?????????? 40????????? 44500.00
8?????????? 50????????? 6500.00
9?????????? 50????????? 7500.00

需求：根據(jù)部門分組，顯示每個(gè)部門的工資等級(jí)

預(yù)期結(jié)果：

empid?????? deptid????? salary????????????????????????????????? rank
----------- ----------- --------------------------------------- --------------------
1?????????? 10????????? 5500.00???????????????????????????????? 1
2?????????? 10????????? 4500.00???????????????????????????????? 2
4?????????? 20????????? 4800.00???????????????????????????????? 1
3?????????? 20????????? 1900.00???????????????????????????????? 2
7?????????? 40????????? 44500.00???????????????????????????????1
6?????????? 40????????? 14500.00???????????????????????????????2
5?????????? 40????????? 6500.00???????????????????????????????? 3
9?????????? 50????????? 7500.00???????????????????????????????? 1
8?????????? 50????????? 6500.00???????????????????????????????? 2

SQL腳本：

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

?

轉(zhuǎn)自： http://www.cnblogs.com/digjim/archive/2006/09/20/509344.html

我們知道，SQL Server 2005和SQL Server 2000 相比較，SQL Server 2005有很多新特性。這篇文章我們要討論其中的一個(gè)新函數(shù)Row_Number()。數(shù)據(jù)庫(kù)管理員和開發(fā)者已經(jīng)期待這個(gè)函數(shù)很久了，現(xiàn)在終于等到了！


?通常，開發(fā)者和管理員在一個(gè)查詢里，用臨時(shí)表和列相關(guān)的子查詢來(lái)計(jì)算產(chǎn)生行號(hào)。現(xiàn)在SQL Server 2005提供了一個(gè)函數(shù)，代替所有多余的代碼來(lái)產(chǎn)生行號(hào)。

?我們假設(shè)有一個(gè)資料庫(kù)[EMPLOYEETEST]，資料庫(kù)中有一個(gè)表[EMPLOYEE]，你可以用下面的腳本來(lái)產(chǎn)生資料庫(kù)，表和對(duì)應(yīng)的數(shù)據(jù)。

?USE [MASTER]
GO

    IF? EXISTS (SELECT NAME FROM SYS.DATABASES WHERE NAME = N'EMPLOYEE TEST')
    

    DROP DATABASE [EMPLOYEE TEST]
    

    GO
  

    CREATE DATABASE [EMPLOYEE TEST]
    

    GO
  

    USE [EMPLOYEE TEST]
    

    GO
  

IF? EXISTS SELECT * FROM SYS.OBJECTS HERE OBJECT_ID = OBJECT_ID(N'[DBO].[EMPLOYEE]') AND TYPE IN (N'U'))
DROP TABLE [DBO].[EMPLOYEE]
GO

    CREATE TABLE EMPLOYEE (EMPID INT, FNAME VARCHAR(50),LNAME VARCHAR(50))
    

    GO
  

    INSERT INTO EMPLOYEE? (EMPID, FNAME, LNAME) VALUES (2021110, 'MICHAEL', '

POLAND')
    

    INSERT INTO EMPLOYEE? (EMPID, FNAME, LNAME) VALUES (2021110, 'MICHAEL', 'POLAND')
    

    INSERT INTO EMPLOYEE? (EMPID, FNAME, LNAME) VALUES (2021115, 'JIM', 'KENNEDY')
    

    INSERT INTO EMPLOYEE? (EMPID, FNAME, LNAME) VALUES (2121000, 'JAMES', 'SMITH')
    

    INSERT INTO EMPLOYEE? (EMPID, FNAME, LNAME) VALUES (2011111, 'ADAM', 'ACKERMAN')
    

    INSERT INTO EMPLOYEE? (EMPID, FNAME, LNAME) VALUES (3015670, 'MARTHA', 'LEDERER')
    

    INSERT INTO EMPLOYEE? (EMPID, FNAME, LNAME) VALUES (1021710, 'MARIAH', 'MANDEZ')
    

    GO
  

?我們可以用下面的腳本查詢EMPLOYEE表。

?SELECT EMPID, RNAME, LNAME FROM EMPLOYEE

?這個(gè)查詢的結(jié)果應(yīng)該如圖1.0?

圖1.0

?在SQL Server 2005，要根據(jù)這個(gè)表中的數(shù)據(jù)產(chǎn)生行號(hào)，我通常使用下面的查詢。?

SELECT ROWID=IDENTITY(int,1,1) , EMPID, FNAME, LNAME INTO EMPLOYEE2 FROM EMPLOYEE ORDER BY EMPID

?這個(gè)查詢創(chuàng)建了一個(gè)新的表，用identify函數(shù)來(lái)產(chǎn)生行號(hào)。我們用下面的查詢來(lái)看看這個(gè)表的數(shù)據(jù)。?

    SELECT ROWID, EMPID, FNAME, LNAME FROM EMPLOYEE2
  

?上面的查詢結(jié)果如圖1.1?

圖1.1

?這個(gè)查詢結(jié)果很明顯EMP=2021110的行是重復(fù)的數(shù)據(jù)。

?要?jiǎng)h除EMPID=2021110的重復(fù)數(shù)據(jù)，我們必須在EMPLOYEE2表中刪除，不能直接在EMPLOYEE中刪除。

?SQL Server 2005提供了一個(gè)新的函數(shù)（Row_Number()）來(lái)產(chǎn)生行號(hào)。我們可以使用這個(gè)新函數(shù)來(lái)刪除原來(lái)表中的重復(fù)數(shù)據(jù)，只用通常的表達(dá)方式再加上Row_Number()函數(shù)。

?讓我們用Row_Number()函數(shù)根據(jù)EMPID來(lái)產(chǎn)生ROWID。

?SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE

?上面的查詢結(jié)果如圖1.2?

圖1.2

?在這個(gè)結(jié)果中，我們可以區(qū)別EMPID是2021110的重復(fù)數(shù)據(jù)。

?我們可以用通用表查詢表達(dá)式和Row_Numner()函數(shù)來(lái)選出重復(fù)的那行數(shù)據(jù)。

?WITH [EMPLOYEE ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE)
SELECT * FROM [EMPLOYEE ORDERED BY ROWID] WHERE ROWID =4

上面的查詢結(jié)果如圖1.3?

圖1.3

?這一行重復(fù)的數(shù)據(jù)可以用下面這個(gè)通用表和Row_Number()函數(shù)來(lái)刪除。

?WITH [EMPLOYEE ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE)
DELETE FROM [EMPLOYEE ORDERED BY ROWID] WHERE ROWID =4

?刪除以后，我們可以用下面的查詢語(yǔ)句看一下結(jié)果。

?SELECT * FROM EMPLOYEE

?這個(gè)查詢結(jié)果如圖1.4?

圖 1.4

?這里我們可以看到，重復(fù)的數(shù)據(jù)已經(jīng)被刪除了。

?總結(jié)

在這篇文章中，我們討論了SQL Server 2005 的新特性Row_Number()函數(shù)，還有通常的表表達(dá)式，然后如何使用這兩個(gè)來(lái)刪除重復(fù)的行。

來(lái)源： http://www.cnblogs.com/fxgachiever/archive/2010/09/15/1826792.html

ROW_NUMBER() OVER函數(shù)的基本用法