ROW_NUMBER() OVER

 /* DATE :  2011-12-13    AUTHOR:  AWINLIU    FUNCTION:  ROW_NUMBER() OVER的使用方法*/--CREATE TABLECREATE TABLE [dbo].[#workers]([ID] [tinyint] IDENTITY(1,1) NOT NULL,[NAME] [varchar](50) NULL,[ADDRE] [varchar](50) NULL,[SALARY] [decimal](18, 2) NULL)--INSERT TEST DATAINSERT INTO #workersSELECT 'A', 'SHANGHAI',100.00 UNIONSELECT 'B', 'WENZHOU',2000.00 UNIONSELECT 'C', 'WUHAN',400.00 UNIONSELECT 'D', 'HANGZHOU',600.00 UNIONSELECT 'E', 'CHONGQING',800.00 UNIONSELECT 'F', 'SUZHOU',300.00 UNIONSELECT 'G', 'CS',988.00--QUERY DATASELECT * FROM #workers--DELETE PART DATA TO TESTDELETE FROM #workers WHERE ID IN(2,5,6)--得到ID序列不连续的指定数据SELECT ID,NAME,ADDRE,SALARY,NUMFROM ( SELECT ID,NAME,ADDRE,SALARY,ROW_NUMBER() OVER(ORDER BY ID DESC) AS NUMFROM #workers) AS workersOrderByIdWHERE NUM BETWEEN 2 AND 3