SQL：在一个字段中按最小值分组，同时选择不同的行 [英] SQL: Group by minimum value in one field while selecting distinct rows

问题描述

这是我想要做的。假设我有这张表：

Here's what I'm trying to do. Let's say I have this table t:

id | record_date | other_cols
18 | 2011-04-03  | x
18 | 2012-05-19  | y
18 | 2012-08-09  | z
19 | 2009-06-01  | a
19 | 2011-04-03  | b
19 | 2011-10-25  | c
19 | 2012-08-09  | d

对于每个id，我想选择包含最小值record_date的行。所以我会得到：

For each id, I want to select the row containing the minimum record_date. So I'd get:

id | record_date | other_cols
18 | 2011-04-03  | x
19 | 2009-06-01  | a

我遇到的唯一解决方案是假设所有record_date条目都是不同的，但是在我的数据中不是这种情况。使用子查询和具有两个条件的内部连接会给我一些重复的行，我不想这样做：

The only solutions I've seen to this problem assume that all record_date entries are distinct, but that is not this case in my data. Using a subquery and an inner join with two conditions would give me duplicate rows for some ids, which I don't want:

id | record_date | other_cols
18 | 2011-04-03  | x
19 | 2011-04-03  | b
19 | 2009-06-01  | a

推荐答案

SELECT mt.*,     
FROM MyTable mt INNER JOIN
    (
        SELECT ID, MIN(Record_Date) MinDate
        FROM MyTable
        GROUP BY ID
    ) t ON mt.ID = t.ID AND mt.Record_Date = t.MinDate

这将获得每个ID的最小日期，然后根据这些值获取值。唯一一次你会有重复的是，如果有相同的ID重复的最低record_dates。

This gets the minimum date per ID, and then gets the values based on those values. The only time you would have duplicates is if there are duplicate minimum record_dates for the same ID.

这篇关于SQL：在一个字段中按最小值分组，同时选择不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！