PostgreSQL中按res_id分组后按last_update排序查询求助

表结构说明 #

• 表A：
  • id（自增主键）
  • res_id（关联表B的外键）
  • description（描述字段）
  • last_update（日期类型，记录更新时间）
• 表B：
  • id（自增整数主键）
  • description（varchar(256)类型的描述字段）

需求与问题分析 #

需求是对表A按res_id分组，同时按last_update日期对分组结果排序。你之前尝试的查询存在两个问题：

1. ORDER BY id last_update语法错误，字段间缺少逗号分隔；
2. 分组后id既不在GROUP BY列表中，也不是聚合函数的结果，无法直接用于排序，且需求是按last_update排序，需要明确取每个分组内的哪一个last_update值（比如最新或最早）来作为排序依据。

正确查询写法 #

场景1：按每个分组的最新更新时间排序（降序） #

如果想让更新时间最新的分组排在前面，可以用MAX(last_update)获取每个分组的最新时间，以此排序：

SELECT count(id) AS record_count, res_id, MAX(last_update) AS latest_update
FROM A
GROUP BY res_id
ORDER BY latest_update DESC;

如果不需要在结果中显示最新更新时间，也可以直接在ORDER BY中使用聚合函数：

SELECT count(id) AS record_count, res_id
FROM A
GROUP BY res_id
ORDER BY MAX(last_update) DESC;

场景2：按每个分组的最早更新时间排序（升序） #

如果想让更新时间最早的分组排在前面，用MIN(last_update)获取每个分组的最早时间：

SELECT count(id) AS record_count, res_id, MIN(last_update) AS earliest_update
FROM A
GROUP BY res_id
ORDER BY earliest_update ASC;

同样，不需要显示最早时间的话可以简化为：

SELECT count(id) AS record_count, res_id
FROM A
GROUP BY res_id
ORDER BY MIN(last_update) ASC;

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