如何结合MAX()、GROUP BY与INNER JOIN实现深度条件SQL查询

你的原SQL语句存在问题：GROUP BY分组后，collar.max_depth既不是聚合函数结果，也不在GROUP BY字段列表中，因此HAVING子句无法直接引用该字段。以下是两种可行的解决方案：

方案一：聚合后关联筛选（仅返回hole_id及对应最大survey深度） #

如果只需要获取每个符合条件的hole_id及其最大survey深度，可先对survey表聚合，再与collar表关联做筛选：

SELECT s.hole_id, s.max_survey_depth
FROM (
    SELECT hole_id, MAX(depth) AS max_survey_depth
    FROM survey
    GROUP BY hole_id
) s
JOIN collar c ON s.hole_id = c.hole_id
WHERE s.max_survey_depth > c.max_depth;

方案二：返回所有符合条件的survey原始记录 #

如果需要返回survey表中所有属于目标hole_id的原始记录（只要该hole_id的最大survey深度超过collar的max_depth），可以用窗口函数实现：

SELECT s.*
FROM (
    SELECT *,
           MAX(depth) OVER (PARTITION BY hole_id) AS max_survey_depth
    FROM survey
) s
JOIN collar c ON s.hole_id = c.hole_id
WHERE s.max_survey_depth > c.max_depth;

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