如何根据唯一字段获取行号，并按其他字段排序

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个表的组合

保存所有研究数据的表" research“

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

保存所有讲师数据的表"Author“

------ 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

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

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

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

我将连接移出子查询以限制查找。