查询每个ID&的最后3条记录明智地使用类别而不使用Row_number() [英] Query get last 3 records per ID & category wise without using Row_number()

问题描述

我有一个历史记录表tbl_history，其中包含记录的部分内容.

表样本数据:-

I have a History Table tbl_history which contains crores of Records.

Table Sample Data:-

id Volume DeletedTime
1  C      11am 
1  C      12am 
1  C      1pm 
1  C      2pm 
1  C      3pm 
1  D      11am 
1  D      12am 
1  D      1pm 
1  D      2pm 
1  D      3pm 
2  C      11am 
2  C      12am 
2  C      1pm 
2  C      2pm 
2  C      3pm 
2  D      11am 
2  D      12am 
2  D      1pm 
2  D      2pm 
2  D      3pm 

现在我想要的是列出每个ID和每个类别的最后3条记录，即在我们的案例卷中，而不使用行号功能，原因是我希望实现除Row_number()之外的其他一些逻辑，因为查询需要很长时间才能执行.

预期输出:-

Now what i want is to list out last 3 Records per ID and per Category i.e in our Case Volume, without using Row number function the reason i am looking to implement some other logic other than Row_number() because query takes long time to execute.

Expected Output:-

id  Volume  DeletedTime
1   C       01:00:00 PM
1   C       02:00:00 PM
1   C       03:00:00 PM
1   D       01:00:00 PM
1   D       02:00:00 PM
1   D       03:00:00 PM
2   C       01:00:00 PM
2   C       02:00:00 PM
2   C       03:00:00 PM
2   D       01:00:00 PM
2   D       02:00:00 PM
2   D       03:00:00 PM

请帮帮我.

问候，
Girish

Please help me out.

Regards,
Girish

推荐答案

如果查询有效，并且您认为查询速度很慢，请尝试为正在查询和连接的列建立索引. ROW_NUMBER并不慢，这是您使用它的方式使其变慢.

If the query works and you think it is slow, try indexing the columns that are being queried and joined. ROW_NUMBER is not slow, it is how you use it that makes it slow.

这篇关于查询每个ID&的最后3条记录明智地使用类别而不使用Row_number()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！