如何在SQL中按记录类型获取最新记录

huangapple go评论87阅读模式
英文:

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

huangapple
  • 本文由 发表于 2020年1月6日 21:22:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/59612915.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定