在sql server中排名函数 [英] Ranking functions in sql server

问题描述

我有一个名为transaction的数据库表，拥有过去5年的交易详情。

我需要一个查询，它提供过去3周内的3次最新交易，如果查询未获得3笔交易过去3周，它应该回到第4周并且最近4次交易，如果在过去4周没有获得最后4次交易，查询应该提取最新的5笔交易。

查询应该执行对于每个部门和该部门下的每个产品

表格结构

I have database table called transaction, Having transactions details from last 5 years.
I need a Query which gives 3- latest transaction from last 3 weeks, if query haven't get 3 transactions from last 3 weeks, it should go back to 4th week and pull latest 4 transactions , if haven't get last 4 transaction in last 4-weeks, Query should pull latest 5 transaction.
The Query should executed for each Dept and for each product under that Dept
table Structure

DeptID ProductID TransactionDate TransactionAmt 
------------------------------------------------------------
1         2
1         2
1         2
1         1 
1         1
1         1

注 - 单周可能有多笔交易或者可能不包含任何交易

我尝试过使用排名功能但是无法提取欲望结果

任何帮助将不胜感激

谢谢提前s

Note - Single Week may have multiple transactions or may not contain any transaction
I have tried using Ranking function but not able to pull desire results
any help would be appreciated
Thanks in advance

推荐答案

试试这个：

Try this:

SELECT *
FROM (
    SELECT DeptID, ProductID, TransactionDate, TransactionAmt, ROW_NUMBER() OVER(ORDER BY TransactionDate DESC PARTITION BY DeptID, ProductID) AS RowNo 
    FROM TableName
) AS T
WHERE RowNo <=3

这篇关于在sql server中排名函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！