Oracle-通过一对多关系，根据最小值选择不同的行 [英] Oracle - With a one to many relationship, select distinct rows based on a min value

问题描述

此问题与在一对多关系中，基于MIN值返回不同的行，但我想看看其他方言，尤其是Oracle中的答案是什么.

This question is the same as In one to many relationship, return distinct rows based on MIN value with the exception that I'd like to see what the answer looks like in other dialects, particularly in Oracle.

从原始描述重新发布:

比方说，一个病人拜访了很多次.我想编写一个查询，该查询根据最早的病人行返回不同的病人行.例如，考虑以下几行.

Let's say a patient makes many visits. I want to write a query that returns distinct patient rows based on their earliest visit. For example, consider the following rows.

patients
-------------
id    name
1     Bob
2     Jim
3     Mary

visits
-------------
id    patient_id    visit_date    reference_number
1     1             6/29/14       09f3be26
2     1             7/8/14        34c23a9e
3     2             7/10/14       448dd90a

查询返回的内容是:

id    name    first_visit_date    reference_number
1     Bob     6/29/14             09f3be26
2     Jim     7/10/14             448dd90a

在另一个问题中，使用postgresql，最好的解决方案似乎是使用distinct on，但这在其他方言中是不可用的.

In the other question, using postgresql, the best solution seemed to be to use distinct on, but that is not available in other dialects.

推荐答案

通常，一个使用row_number():

select id, name, visit_date as first_visit_date, reference_number
from (select v.id, p.name, v.visit_date, v.reference_number,
             row_number() over (partition by p.id order by v.visit_date desc) as seqnum
      from visits v join
           patients p
           on v.patient_id p.id
     ) t
where seqnum = 1;

这篇关于Oracle-通过一对多关系，根据最小值选择不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！