通过 report_id 获取前 'n' 条记录 [英] Get top 'n' records by report_id

问题描述

我的 MSSQL 数据库中有一个简单的视图.它由以下字段组成:

I have a simple view in my MSSQL database. It consists of the following fields:

report_id INT
ym VARCHAR -- YYYY-MM
keyword VARCHAR(MAX)
visits INT

我可以通过以下查询轻松获取前 10 个关键字命中:

I can easily get the top 10 keyword hits with the following query:

SELECT TOP 10 *
FROM top_keywords
WHERE ym BETWEEN '2010-05' AND '2010-05'
ORDER BY visits DESC

现在变得棘手的是我必须在给定的日期范围内为每个 report_id 获取前 10 条记录(ym BETWEEN @start_date AND @end_date).

Now where it gets tricky is where I have to get the top 10 records for each report_id in the given date range (ym BETWEEN @start_date AND @end_date).

我将如何获得每个 report_id 的前 10 名?我偶然发现了一些涉及使用 ROW_NUMBER() 和 RANK() 的建议，但在它们的实施中都极不成功.

How would I go about getting the top 10 for each report_id? I've stumbled across suggestions involving the use of ROW_NUMBER() and RANK(), but have been vastly unsuccessful in their implementation.

推荐答案

怎么样

SELECT *
FROM (SELECT *,
         ROW_NUMBER() OVER(PARTITION BY report_id ORDER BY (SELECT 0)) AS RN
         FROM top_keywords
         WHERE ym BETWEEN '2010-05' AND '2010-05') TK
WHERE RN <= 10

这篇关于通过 report_id 获取前 'n' 条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！