从同一表的每个类别中选择前n条记录 [英] Select top n records from each category within same table

问题描述

我有一个包含item id，purchase date和item unit cost的购买明细表.

I've a purchase detail table that has item id, purchase date, and item unit cost.

我想通过从每个商品ID中选择最新的前2条记录来平均计算商品购买成本.

I want to get an avg of an item purchase cost by selecting latest top 2 records from each item id.

Item id, purchase date, unitprice
1        3/1/2012        10
1        3/11/2012        8
2        3/1/2012        10
2        3/11/2012        10
1        2/1/2012        9
3        3/1/2012        10
3        3/11/2012        1
3        3/12/2012        13

我正在使用sql server 2008 r2

I'm using sql server 2008 r2

推荐答案

尝试一下:

;WITH CTE AS (
    SELECT [Item id], [purchase date], unitprice, 
        ROW_NUMBER() OVER(PARTITION BY [Item id] ORDER BY [purchase date] DESC) rn
    FROM your_table
)
SELECT [Item id], [purchase date], unitprice
FROM CTE
WHERE rn < 3

我不确定如何用最近的两条记录来计算平均值，但是如果需要，可以确保将GROUP BY添加到查询中.

I'm not sure how can you calc avg with latest two records, but sure you can add GROUP BY to the query if you need.

也许是这样:

-- CTE here --
SELECT [Item id], AVG(unitprice)
FROM CTE
WHERE rn < 3
GROUP BY [Item id]

这篇关于从同一表的每个类别中选择前n条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！