T-SQL:按列获取前n条记录

Struggling with what's probably a very simple problem. I have a query like this:

这可能是一个很简单的问题。我有这样一个问题:

 ;WITH rankedData
       AS ( -- a big, complex subquery)
  SELECT UserId,
         AttributeId,
         ItemId
  FROM   rankedData
  WHERE  rank = 1
  ORDER  BY datEventDate DESC

The sub-query is designed to grab a big chunk of interlined data and rank it by itemId and date, so that the rank=1 in the above query ensures we only get unique ItemIds, ordered by date. The partition is:

子查询的设计目的是获取一大块内联数据，并按itemId和日期对其进行排序，以便上面查询中的rank=1确保我们只获得按日期排序的唯一itemId。分区:

Rank() OVER (partition BY ItemId ORDER BY datEventDate DESC) AS rk

The problem is that what I want is the top 75 records for each UserID, ordered by date. Seeing as I've already got a rank inside my sub-query to sort out item duplicates by date, I can't see a straightforward way of doing this.

问题是，我想要的是每个用户id的前75条记录，按日期排序。由于我在子查询中已经有了一个级别，可以按日期对项目重复项进行排序，因此我无法看到一种简单的方法。

Cheers, Matt

欢呼,马特

1 个解决方案

#1

I think your query should look like

我认为您的查询应该是这样的

SELECT t.UserId, t.AttributeId, t.ItemId
FROM (
SELECT UserId, AttributeId, ItemId, rowid = ROW_NUMBER() OVER (
        PARTITION BY UserId ORDER BY datEventDate 
        ) 
FROM rankedData
) t
WHERE t.rowid <= 75