SQL 分组排序分页（大神帮写的膜拜一下）

查询全部：

SELECT P3.ID, P3.Name, P3.AddTime
FROM (SELECT Name, MAX(AddTime) AS MaxAddTime
FROM Product AS P1
GROUP BY Name) AS P2
INNER JOIN Product AS P3 ON P2.Name = P3.Name
ORDER BY P2.MaxAddTime DESC, P3.AddTime DESC, P3.ID DESC

正反TOP方法的分页（每页5行，查第5页，即第21-25行数据）
(1)按要求的顺序取前25行，(2)颠倒过来取前5行，(3)再把顺序颠倒回来

SELECT P5.ID, P5.Name, P5.MaxAddTime
FROM
(SELECT TOP 5 P4.*
FROM
(SELECT TOP 25 P3.ID, P3.Name, P3.AddTime, P2.MaxAddTime
FROM
(SELECT Name, MAX(AddTime) AS MaxAddTime
FROM Product AS P1
GROUP BY Name) AS P2
INNER JOIN Product AS P3 ON P2.Name = P3.Name
ORDER BY P2.MaxAddTime DESC, P3.AddTime DESC, P3.ID DESC) AS P4
ORDER BY P4.MaxAddTime, P4.AddTime, P4.ID) AS P5
ORDER BY P5.MaxAddTime DESC, P5.AddTime DESC, P5.ID DESC

 

ROW_NUMBER方法（适用于SQL Server 2005以上）
（每页5行，查第5页，即第21-25行数据）

SELECT P4.ID, P4.Name, P4.AddTime
FROM
(SELECT P3.ID, P3.Name, P3.AddTime, ROW_NUMBER() OVER (ORDER BY P2.MaxAddTime DESC, P3.AddTime DESC, P3.ID DESC) AS RN
FROM
(SELECT Name, MAX(AddTime) AS MaxAddTime
FROM Product AS P1
GROUP BY Name) AS P2
INNER JOIN Product AS P3 ON P2.Name = P3.Name) AS P4
WHERE P4.RN BETWEEN 21 AND 25

转载于:https://www.cnblogs.com/hpuCode/p/4122710.html