SQL SERVER排名函数 [英] SQL SERVER Ranking functions

问题描述

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



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

查询应该针对每个部门以及该部门下的每个产品执行



表格结构



DeptID ProductID TransactionDate TransactionAmt

------------------------------------ ------------------------



2 2

1 2

1 2

1 1

1 1

1 1



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



我尝试过使用排名功能但不能拉欲望结果



任何帮助将不胜感激



提前致谢

解决方案

以下是您的答案：在sql server中排名函数 [ ^

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

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

解决方案

Here is your answer: Ranking functions in sql server[^]

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