SQL Server加入最新的2个条目

I know the title of the post is bad but hear me out. A question like this arose the other day at work, and while I found a way around it, the problem still haunts me.

我知道帖子的标题很糟糕,但是听我说。这样的问题出现在工作的另一天,当我找到解决方法时,问题仍然困扰着我。

Lets assume * has only 3 tables.

让我们假设*只有3个表。

Users ( username )
Comments ( comment, creationdate )
UsersCommentsJoin , this is the join table between the first 2 tables.

Now lets say I want to make a query that would return the all the users with the last 2 most recent comments. So the result set would look like this.

现在让我们说我想创建一个查询,返回所有用户最近2条评论。所以结果集看起来像这样。

|username| most recent comment | second most recent comment|

How on earth do I go about creating that query ? I solved this problem earlier by simply only returning the most recent comment and not even trying to get the second one, and boy, let me tell you it seemed a WHOLE lot more involved than when I thought with subselects, TOP and other weird DB acrobatics.

我该如何创建该查询呢?我之前只通过回复最近的评论而不是试图获得第二个评论来解决这个问题,而且男孩,让我告诉你它似乎比我想到的子选择,TOP和其他奇怪的数控杂技更多参与。

Bonus Round Why do some queries which seem easy logically, turn out to be monster queries, at least from my rookie perspective ?

奖金回合为什么一些看起来很容易逻辑的查询,结果是怪物查询,至少从我的菜鸟角度来看?

EDIT: I was using an MS SQL server.

编辑:我使用的是MS SQL服务器。

1 个解决方案

#1

You can use a crosstab query pivoting on ROW_NUMBER

您可以使用在ROW_NUMBER上旋转的交叉表查询

WITH UC 
     AS (SELECT UCJ.userId, 
                C.comment, 
                ROW_NUMBER() OVER (PARTITION BY userId 
                                       ORDER BY creationdate DESC) RN 
         FROM   UsersCommentsJoin UCJ 
                JOIN Comments C 
                  ON C.commentId = U.commentId) 
SELECT username, 
       MAX(CASE 
             WHEN RN = 1 THEN comment 
           END) AS MostRecent, 
       MAX(CASE 
             WHEN RN = 2 THEN comment 
           END) AS SecondMostRecent 
FROM   Users U 
       JOIN UC 
         ON UC.userId = U.userId 
WHERE  UC.RN <= 2 
GROUP  BY UC.userId