SQL分组查询问题：非聚合字段显示及关联表最值对应字段获取

这是SQL分组场景里的常见困扰——默认情况下，GROUP BY执行后，SELECT列表里只能放分组字段，或者经过SUM()/MAX()这类聚合函数处理后的结果，直接写其他非分组字段大概率会报错（除非数据库关闭了严格模式，但这种操作非常不安全，结果完全不可控）。下面给你几种靠谱的解决方案：

• 子查询+关联表：先通过分组查询得到聚合结果，再把这个结果和原表（或关联表）连接，从而获取需要的其他字段。比如假设你有个orders表（user_id, order_id, amount, user_name），想按用户分组，显示用户ID、总订单金额、用户姓名：

  SELECT DISTINCT o.user_id, o.user_name, agg.total_amount
  FROM orders o
  JOIN (
      SELECT user_id, SUM(amount) AS total_amount
      FROM orders
      GROUP BY user_id
  ) agg ON o.user_id = agg.user_id;
  

  这里先通过子查询拿到每个用户的总金额，再关联原表提取用户名，用DISTINCT确保每个用户只显示一行。

• 窗口函数：用ROW_NUMBER()、RANK()这类窗口函数，按分组字段分区，再按你需要的规则排序，取每组的指定行，就能拿到该行的所有字段。比如要获取每个用户最新的订单完整信息（包括订单号、金额）：

  SELECT user_id, order_id, amount, user_name
  FROM (
      SELECT 
          user_id, order_id, amount, user_name,
          ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn
      FROM orders
  ) ranked_orders
  WHERE rn = 1;
  

  窗口函数会给每个用户的订单按创建时间降序编号，rn=1就是最新的那一行，自然能拿到该行的所有字段。

• 避坑提醒：千万别为了省事关闭ONLY_FULL_GROUP_BY模式，这种情况下数据库会随机返回分组内某一行的非聚合字段值，结果完全不可预测，很容易引发业务bug。

你已经搞定了最小和最大时间的查询，现在要补充最大时间对应的IDState，这里给你两种实用方法：

方法一：窗口函数（推荐，简洁可控） #

用窗口函数同时计算每个Job的最小时间，再给每个Job的时间行按降序编号，取编号为1的行就是最大时间对应的记录：

WITH job_time_details AS (
    SELECT 
        IDJob,
        JobTime,
        IDState,
        -- 计算每个Job的最小时间
        MIN(JobTime) OVER (PARTITION BY IDJob) AS min_job_time,
        -- 按Job分组，时间降序编号，第一行就是最大时间
        ROW_NUMBER() OVER (PARTITION BY IDJob ORDER BY JobTime DESC) AS rn
    FROM JOBTIMES
)
SELECT 
    j.IDJob,
    j.Notes,
    jtd.min_job_time,
    jtd.JobTime AS max_job_time,
    jtd.IDState
FROM JOBS j
JOIN job_time_details jtd ON j.IDJob = jtd.IDJob
WHERE jtd.rn = 1;

如果同一个Job存在多个相同的最大JobTime，ROW_NUMBER()会随机选一行；如果想返回所有符合最大时间的行，可以换成RANK()，然后把条件改成rn <= 1。

方法二：子查询关联（兼容老版本数据库） #

如果你的数据库不支持CTE或窗口函数，可以用多层子查询实现：先拿到每个Job的最大时间，关联JOBTIMES拿到对应的IDState，再关联另一个子查询拿到最小时间：

SELECT 
    j.IDJob,
    j.Notes,
    min_jt.min_job_time,
    max_jt.JobTime AS max_job_time,
    max_jt.IDState
FROM JOBS j
-- 关联最小时间的子查询
LEFT JOIN (
    SELECT IDJob, MIN(JobTime) AS min_job_time
    FROM JOBTIMES
    GROUP BY IDJob
) min_jt ON j.IDJob = min_jt.IDJob
-- 关联最大时间及对应IDState的子查询
LEFT JOIN (
    SELECT jt.IDJob, jt.JobTime, jt.IDState
    FROM JOBTIMES jt
    JOIN (
        SELECT IDJob, MAX(JobTime) AS max_job_time
        FROM JOBTIMES
        GROUP BY IDJob
    ) max_sub ON jt.IDJob = max_sub.IDJob AND jt.JobTime = max_sub.max_job_time
) max_jt ON j.IDJob = max_jt.IDJob;

如果存在多个相同最大时间的行，这个查询会返回多行，你可以在max_jt子查询里加DISTINCT，或者用LIMIT 1（根据数据库语法调整）来控制返回行数。

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