英文:
How to get latest records for by records type in SQL
问题
以下是您要求的翻译：
我的表格如下所示：
ID NAME TYPE INSERTION_DATE OCCURRANCE_DATE
I100 ABC WEEKLY 2019-12-31 2019-12-30
I100 ABC WEEKLY 2019-12-22 2019-12-20
I101 DEF MONTHLY 2019-12-31 2019-12-30
I101 DEF MONTHLY 2019-12-01 2019-11-29
我如何使用SQL查询根据OCCURANCE_DATE获取每种类型（Weekly和Monthly）的最新记录。结果应如下所示：
ID NAME TYPE INSERTION_DATE OCCURRANCE_DATE
I100 ABC WEEKLY 2019-12-31 2019-12-30
I101 DEF MONTHLY 2019-12-31 2019-12-30
英文:
My table looks like:
ID NAME TYPE INSERTION_DATE OCCURRANCE_DATE
I100 ABC WEEKLY 2019-12-31 2019-12-30
I100 ABC WEEKLY 2019-12-22 2019-12-20
I101 DEF MONTHLY 2019-12-31 2019-12-30
I101 DEF MONTHLY 2019-12-01 2019-11-29
How I can get latest records based on OCCURANCE_DATE for each type (Weekly & monthly) using SQL query. The result should be like this:
ID NAME TYPE INSERTION_DATE OCCURRANCE_DATE
I100 ABC WEEKLY 2019-12-31 2019-12-30
I101 DEF MONTHLY 2019-12-31 2019-12-30
答案1
得分: 1
以下是已翻译的内容：
相关子查询是一种简单的方法：
select t.*
from t
where t.OCCURANCE_DATE = (select max(t2.OCCURANCE_DATE)
from t t2
where t2.type = t.type
);
英文:
A correlated subquery is a simple method:
select t.*
from t
where t.OCCURANCE_DATE = (select max(t2.OCCURANCE_DATE)
from t t2
where t2.type = t.type
);
答案2
得分: 1
有很多选项可以解决这个按组的最大n值问题。
你可以使用关联子查询进行筛选：
select t.*
from mytable t
where t.occurence_date = (
select max(t1.occurence_date)
from mytable t1
where t1.type = t.type
)
另一个典型的解决方案是反向的left join：
select t.*
from mytable t
left join mytable t1 on t1.type = t.type and t1.occurence_date > t.occurence_date
where t1.id is null
英文:
There are many options to address this greatest-n-per-group question.
You can filter with a correlated subquery:
select t.*
from mytable t
where t.occurence_date = (
select max(t1.occurence_date)
from mytable t1
where t1.type = t.type
)
Another typical solution is the anti-left join:
select t.*
from mytable t
left join mytable t1 on t1.type = t.type and t1.occurence_date > t.occurence_date
where t1.id is null
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论