在一对多关系中，根据MIN值返回不同的行 [英] In one to many relationship, return distinct rows based on MIN value

问题描述

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

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

我尝试过的内容如下:

SELECT
  patients.id, 
  patients.name, 
  visits.visit_date AS first_visit_date, 
  visits.reference_number
FROM
  patients
INNER JOIN (
  SELECT
    *
  FROM
    visits
  ORDER BY
    visit_date
  LIMIT
    1
) visits ON
  visits.patient_id = patients.id

添加LIMIT会导致查询返回0行，但是删除它会导致查询返回重复行.这有什么窍门?我还尝试过在INNER JOIN中选择MIN(visit_date)，但这也返回了dups.

Adding the LIMIT causes the query to return 0 rows, but removing it causes the query to return duplicates. What's the trick here? I've also tried selecting MIN(visit_date) in the INNER JOIN but that's also returning dups.

更新

有人建议这个问题是重复的，但对我来说似乎是不同的，因为我是在两个单独的表上进行的.在另一个问题上，可接受的答案建议在y.max_total = x.total上进行联接，如果要联接的表与从中选择的表相同，则该联接起作用.另外，我需要从行中返回带有MIN日期的其他列，而不仅仅是日期本身.

It's been suggested that this question is a duplicate, but to me it seems different because I'm doing this over two separate tables. The accepted answer on the other question suggests joining on y.max_total = x.total, which works if the table being joined is the same table being selected from. Additionally, I need to return other columns from the row with the MIN date, not just the date itself.

但是，我接受的答案很好.

The answer I've accepted works great, however.

推荐答案

使用distinct on

select distinct on (p.id)
    p.id, 
    p.name, 
    v.visit_date as first_visit_date, 
    v.reference_number
from
    patients p
    inner join
    visits v on p.id = v.patient_id
order by p.id, v.visit_date

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

这篇关于在一对多关系中，根据MIN值返回不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！