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.

让我们假设Stackoverflow只有3个表.

Lets assume Stackoverflow has only 3 tables.

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

现在让我说我想进行一次查询，该查询将返回所有具有最近2条最近注释的用户.结果集看起来像这样.

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.

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

究竟该如何创建该查询?我通过仅返回最新评论而不是试图获得第二条评论来解决了这个问题，男孩，我告诉你，与我认为使用子选择，TOP和其他怪异的DB杂技相比，这似乎涉及了很多

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.

奖金回合为什么至少从我的菜鸟的角度来看，为什么有些逻辑上看起来很容易的查询却变成了怪物查询?

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

我正在使用MS SQL服务器.

I was using an MS SQL server.

答

您可以使用以 ROW_NUMBER

You can use a crosstab query pivoting on 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

SQL Server加入最新的2个条目

相关推荐