sql-server - 如何根据唯一字段获取行号并按另一个字段排序

Question

我想使用 MS SQL 获得这种结果

PAGE 1
ID     Research Title       Published Date    Author ID   Author Name
1      Research A           2013-5-10          D567           John
1      Research A           2013-5-10          D568           Stan
2      Research B           2013-5-9           D569           Steve
3      Research C           2013-5-8           D570           Michael

PAGE 2
ID     Research Title       Published Date    Author ID   Author Name
4      Research D           2013-5-8           D567           John
5      Research F           2013-5-7           D570           Michael
5      Research F           2013-5-7           D568           Stan    
5      Research F           2013-5-7           D569           Steve    
6      Research E           2013-5-6           D569           Steve

结果是3个Table的组合

保存所有研究数据的“研究”表

------ TABLE Research------
- ResearchID Uniqueidentifier
- ResearchTitle text
- PublishedDate

保存所有讲师数据的表“作者”

------ TABLE Author ------
- AuthorID varchar(20)
- AuthorName vachar(100)

并且，表“ResearchAuthorMapping”创建“研究”和“作者”之间的关系

------ TABLE ResearchAuthorMapping------
- ResearchID uniqueidentifier    
- AuthorID varchar(20)

这是我到目前为止的查询

DECLARE @offset int;
DECLARE @limit int = 3;
DECLARE @page int = 1 -- CHANGE BY REQUEST
SET @offset = ((@page - 1) * @limit) + 1;
SELECT * 
FROM
(
    SELECT
        DENSE_RANK() OVER(ORDER BY Research.ResearchTitle DESC) AS num,
        Research.*
    FROM
        Research
        LEFT JOIN
            ResearchAuthorMapping ON Research.ResearchID = ResearchAuthorMapping.ResearchID
        LEFT JOIN
            Author ON ResearchAuthorMapping.AuthorID = Author.AuthorID

)TempTable
WHERE
    TempTable.num >= @Offset AND TempTable.num < @offset + @limit

到目前为止，我只能做到这一点

PAGE 1
ID     Research Title       Published Date    Author ID   Author Name
1      Research F           2013-5-7           D570           Michael
1      Research F           2013-5-7           D568           Stan
1      Research F           2013-5-7           D569           Steve    
2      Research E           2013-5-6           D569           Steve    
3      Research D           2013-5-8           D567           John

PAGE 2
ID     Research Title       Published Date    Author ID   Author Name
4      Research C           2013-5-8           D570           Michael
5      Research B           2013-5-9           D569           Steve
6      Research A           2013-5-10          D567           John
6      Research A           2013-5-10          D568           Stan

按研究标题排序的结果集，

但是，我想要的是按发布日期排序的数据，但根据

获取 ID

ResearchTitle/ResearchID 分组(在我的查询中，我使用 DENSE_RANK 获取 ID)。我使用 ID 来限制结果(分页)

*抱歉我的英语不好

Answer 1

我认为您可以将结果包装在 row_number 集中，对初始结果进行重新排序。大致如下:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY PublishedDate ) AS rownum
                   ,A.*
          FROM      ( SELECT    DENSE_RANK() OVER ( ORDER BY Research.ResearchTitle DESC ) AS num
                               ,ResearchID
                               ,PublishedDate
                      FROM      Research ) A ) B
LEFT JOIN ResearchAuthorMapping C
ON      B.ResearchID = C.ResearchID
LEFT JOIN Author D
ON      C.AuthorID = D.AuthorID
WHERE   B.rownum >= @Offset
        AND B.rownum < @offset + @limit

我将连接移到子查询之外以限制查找。