GROUP BY报错：非分组/非聚合字段的SQL全列查询解决方案

我有一张名为sub_table的表，结构和数据如下：

查询目标1 #

• 基于main_table_id筛选数据
• 按team分组，取每组内created_datetime最新的1条数据
• 返回所有字段

预期结果 #

[
  {
    "id": 2, "created_datetime": "2023-11-02 02:00:00", ...
  },
  {
    "id": 4, "created_datetime": "2023-11-04 04:00:00", ...
  }
]

查询目标2 #

• 基于main_table_id和team筛选数据
• 按created_datetime倒序排序返回所有字段
• 返回所有字段

预期结果（以main_table_id = 1, team = a为例） #

[
  {
    "id": 2, "created_datetime": "2023-11-02 02:00:00", ...
  },
  {
    "id": 1, "created_datetime": "2023-11-01 01:00:00", ...
  }
]

尝试过的查询语句 #

SELECT * FROM [table] GROUP BY team WHERE main_table_id = $1 AND team = $2

或者：

SELECT t1.* 
  FROM (
  SELECT team 
  FROM sub_table 
  GROUP BY team ORDER created_datetime DESC LIMIT 1
) t0 JOIN sub_table t1 
ON t0.team = t1.team WHERE t1.main_table_id = $1;

遇到的错误 #

must appear in the GROUP BY clause or be used in an aggregate function

解决方法 #

针对查询目标1

使用窗口函数ROW_NUMBER()实现分组取最新记录，无需直接GROUP BY所有字段：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY team ORDER BY created_datetime DESC) AS rn
    FROM sub_table
    WHERE main_table_id = $1  -- 替换为目标main_table_id值
) AS t
WHERE rn = 1;

原理：PARTITION BY team按团队分组，ORDER BY created_datetime DESC让每组内最新记录排在首位，ROW_NUMBER()给每组记录编号，取rn=1即可得到每组最新的完整数据，规避GROUP BY的字段限制。

针对查询目标2

此需求无需GROUP BY，直接筛选后排序即可：

SELECT *
FROM sub_table
WHERE main_table_id = $1 AND team = $2  -- 替换为目标参数值
ORDER BY created_datetime DESC;

原理：通过WHERE筛选出指定main_table_id和team的数据集后，直接按created_datetime倒序排列，就能得到预期的排序结果，避免触发GROUP BY的字段校验错误。

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