如何在分组聚合查询中获取匹配最大p.prep_date的c.id值？

你的问题核心在于：SELECT子句中包含了c.id，但GROUP BY仅指定了p.pond_id——这不符合SQL分组规则（部分宽松模式的数据库可能允许，但返回的c.id是随机的，并非对应最大prep_date的目标记录）。要获取每个pond_id最大prep_date对应的c.id，可以用以下两种方法实现：

方法1：子查询+关联查询 #

先通过子查询锁定每个pond_id符合日期条件的最大prep_date，再关联回原表和关联表，精准匹配对应的c.id：

-- 第一步：筛选每个pond_id的最大有效prep_date
WITH max_prep_data AS (
    SELECT 
        p.pond_id,
        MAX(p.prep_date) AS max_pdt
    FROM pond_prep_soils2 p
    JOIN ponds pd ON p.pond_id = pd.id
    JOIN crops c ON c.pond_id = pd.id
    WHERE p.prep_date < c.cycle_start_date 
      AND p.prep_date < c.cycle_end_date
    GROUP BY p.pond_id
)
-- 第二步：关联回原表，获取对应最大日期的c.id
SELECT 
    mpd.max_pdt AS pdt,
    mpd.pond_id,
    c.id
FROM max_prep_data mpd
JOIN pond_prep_soils2 p 
    ON mpd.pond_id = p.pond_id AND mpd.max_pdt = p.prep_date
JOIN ponds pd ON p.pond_id = pd.id
JOIN crops c ON c.pond_id = pd.id
WHERE p.prep_date < c.cycle_start_date 
  AND p.prep_date < c.cycle_end_date;

方法2：窗口函数（推荐） #

使用ROW_NUMBER()窗口函数，给每个pond_id的记录按prep_date降序编号，直接取编号为1的记录（即每组中最大日期的那条）：

SELECT pdt, pond_id, c_id
FROM (
    SELECT 
        p.prep_date AS pdt,
        p.pond_id,
        c.id AS c_id,
        -- 按pond_id分组，prep_date降序排序，每组第一条记录编号为1
        ROW_NUMBER() OVER (PARTITION BY p.pond_id ORDER BY p.prep_date DESC) AS rn
    FROM pond_prep_soils2 p
    JOIN ponds pd ON p.pond_id = pd.id
    JOIN crops c ON c.pond_id = pd.id
    WHERE p.prep_date < c.cycle_start_date 
      AND p.prep_date < c.cycle_end_date
) temp
WHERE temp.rn = 1;

额外注意事项 #

• 你原语句混合使用了隐式连接（逗号分隔表）和显式LEFT JOIN，容易导致连接逻辑混乱，建议统一使用显式JOIN语法明确关联关系。
• 如果存在同一个pond_id+最大prep_date对应多条c.id的情况，ROW_NUMBER()会随机返回一条；若要返回所有匹配记录，可改用RANK()或DENSE_RANK()替换ROW_NUMBER()。

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