如何按InstanceID分组查询每组最新日期对应的TotalProjects数据

问题根因 #

你现有语句的GROUP BY同时包含了InstanceID和TotalProjects，会将同一个InstanceID下不同TotalProjects的记录拆分为多个独立分组，最终返回的是每个InstanceID下每类TotalProjects对应的最大日期，不符合「按InstanceID去重，仅保留最新日期对应全量记录」的需求。

解决方案 #

写法1：窗口函数写法（兼容PostgreSQL/MySQL 8+/SQL Server/Oracle等主流新版本数据库） #

按InstanceID分组后组内按日期倒序排序，取排序第一位的记录即可：

SELECT "InstanceID", "InstanceDate" AS "Date", "TotalProjects"
FROM (
    SELECT 
        "InstanceID",
        "InstanceDate",
        "TotalProjects",
        -- 按InstanceID分组，组内按日期倒序排序
        ROW_NUMBER() OVER (PARTITION BY "InstanceID" ORDER BY "InstanceDate" DESC) AS rn
    FROM "BI_InstanceMetric"
) t
WHERE rn = 1
ORDER BY "InstanceID";

注意：如果同一个InstanceID存在多条相同最新日期的记录，需要全部保留的话，把ROW_NUMBER()替换为RANK()即可。

写法2：关联子查询写法（兼容MySQL 5.x等不支持窗口函数的低版本数据库） #

先查出每个InstanceID对应的最大日期，再关联原表匹配对应记录：

SELECT a."InstanceID", a."InstanceDate" AS "Date", a."TotalProjects"
FROM "BI_InstanceMetric" a
INNER JOIN (
    -- 先获取每个InstanceID的最新日期
    SELECT "InstanceID", MAX("InstanceDate") AS max_date
    FROM "BI_InstanceMetric"
    GROUP BY "InstanceID"
) b ON a."InstanceID" = b."InstanceID" AND a."InstanceDate" = b.max_date
ORDER BY a."InstanceID";

内容的提问来源于stack exchange，提问作者no name sry