MSSQL使用窗口函数查询去重数据

USE ReportServer
GO

--DROP TABLE TB_Books

 1.创建表

CREATE TABLE TB_Books
(
    FID INT IDENTITY(1,1) PRIMARY KEY
    ,FName NVARCHAR(400) NOT NULL
    ,FCategory NVARCHAR(40) NOT NULL
    ,FCreateDate DATETIME NOT NULL
)

2. 创建初始化数据

INSERT TB_Books
VALUES
('AAA','bbb','2020-01-11')
,('flask','python','2020-02-11')
,('pandas','python','2020-03-11')
,('numpy','python','2020-02-21')
,('ASP.NET Core','.net','2020-02-01')
,('ASP.NET','.net','2010-02-11')
,('WPF','.net','2020-12-11')
,('MSSQL','db','2020-5-11')
,('MYSQL','db','2020-6-11')
,('MONGODB','db','2020-07-11')

3. 查询表数据

SELECT * FROM TB_Books;

显示结果：

 4. 使用窗口函数分组，并取分组中第一条数据

WITH T AS
(
　　SELECT 
　　　　ROW_NUMBER() OVER(PARTITION BY FCategory ORDER BY FCreateDate DESC) AS A,
　　　　ROW_NUMBER() OVER( ORDER BY FCreateDate DESC) AS B,
　　　　TB.*
　　FROM TB_Books AS TB
)
SELECT

　　FName, A, B

FROM T 
  WHERE 
　　A=1 -- 按FCategory分组数据中，取第一条数据
-- AND
--　　T.B BETWEEN 1 AND 20;

显示结果：