ROW_NUMBER() OVER

最新推荐文章于 2023-05-22 20:24:24 发布

置顶 xmlhaha

最新推荐文章于 2023-05-22 20:24:24 发布

阅读量385

点赞数

分类专栏： SQL 文章标签： null function table date c

本文链接：https://blog.csdn.net/awinliu/article/details/7066683

版权

SQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

 /* DATE :  2011-12-13
    AUTHOR:  AWINLIU
    FUNCTION:  ROW_NUMBER() OVER的使用方法*/
--CREATE TABLE
CREATE 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 DATA
INSERT INTO #workers
SELECT 'A', 'SHANGHAI',100.00 UNION
SELECT 'B', 'WENZHOU',2000.00 UNION
SELECT 'C', 'WUHAN',400.00 UNION
SELECT 'D', 'HANGZHOU',600.00 UNION
SELECT 'E', 'CHONGQING',800.00 UNION
SELECT 'F', 'SUZHOU',300.00 UNION

SELECT 'G', 'CS',988.00

--QUERY DATA
SELECT * FROM #workers
--DELETE PART DATA TO TEST

DELETE FROM #workers WHERE ID IN(2,5,6)

--得到ID序列不连续的指定数据
SELECT ID,NAME,ADDRE,SALARY,NUM
FROM ( SELECT ID,NAME,ADDRE,SALARY,ROW_NUMBER() OVER(ORDER BY ID DESC) AS NUM
FROM #workers) AS workersOrderById
WHERE NUM BETWEEN 2 AND 3