分组获取各物种最早日期对应ID及关联表查询需求

嘿 Anders，我来帮你搞定这个问题！先说说你之前的查询为什么没得到预期结果——在MySQL（我猜你用的是它）里，直接GROUP BY species再排序的话，ORDER BY是对分组后的整个结果集排序，而不是分组内部先排序再选行；子查询先排序再分组的方式也不靠谱，因为MySQL的查询优化器经常会忽略子查询里的排序，最终分组还是会随机选每组里的一行，所以才拿到了晚的日期。

下面分两种场景给你正确的解法：

一、最初的简单表场景（单表：id、species、date） #

方法1：子查询+关联（兼容所有MySQL版本） #

先通过子查询找出每个物种的最早日期，再和原表关联匹配对应的ID：

SELECT t.id, t.species, t.date
FROM `table` t
INNER JOIN (
    -- 先拿到每个物种的最小日期
    SELECT species, MIN(date) AS min_date
    FROM `table`
    GROUP BY species
) sub 
ON t.species = sub.species AND t.date = sub.min_date;

如果同一个物种有多个ID对应同一个最早日期，这个查询会返回所有符合的行；要是你只需要其中一个，可以在最后加LIMIT 1或者结合窗口函数调整。

方法2：窗口函数（MySQL 8.0+ 或其他支持的数据库） #

用ROW_NUMBER()窗口函数给每个物种的行按日期升序编号，取编号为1的行就是最早日期的记录：

SELECT id, species, date
FROM (
    SELECT 
        id, 
        species, 
        date,
        -- 按物种分组，日期升序排，给每行编号
        ROW_NUMBER() OVER (PARTITION BY species ORDER BY date ASC) AS rn
    FROM `table`
) sub
WHERE rn = 1;

如果想保留所有相同最早日期的记录，把ROW_NUMBER()换成RANK()或者DENSE_RANK()就行。

二、更新后的复杂场景（关联obs和trips表，特定user_id） #

现在需要关联两张表，并且只针对特定用户查询，解法类似，只是多了表关联和user_id过滤：

方法1：子查询+关联（兼容所有版本） #

SELECT o.id, o.species, tr.dato AS date
FROM obs o
INNER JOIN trips tr ON o.trip_id = tr.trip_id
INNER JOIN (
    -- 先过滤特定用户，拿到该用户每个物种的最早日期
    SELECT o.species, MIN(tr.dato) AS min_date
    FROM obs o
    INNER JOIN trips tr ON o.trip_id = tr.trip_id
    WHERE o.user_id = 你的用户ID -- 替换成实际user_id
    GROUP BY o.species
) sub 
ON o.species = sub.species AND tr.dato = sub.min_date
WHERE o.user_id = 你的用户ID; -- 再次过滤用户

方法2：窗口函数（更简洁） #

SELECT id, species, date
FROM (
    SELECT 
        o.id, 
        o.species, 
        tr.dato AS date,
        -- 按物种分组，日期升序编号
        ROW_NUMBER() OVER (PARTITION BY o.species ORDER BY tr.dato ASC) AS rn
    FROM obs o
    INNER JOIN trips tr ON o.trip_id = tr.trip_id
    WHERE o.user_id = 你的用户ID -- 替换成实际user_id
) sub
WHERE rn = 1;

同样，要是同一个物种同一日期有多个观测记录，用RANK()替代ROW_NUMBER()就能保留所有符合的行。

内容的提问来源于stack exchange，提问作者Anders Olsen