SQL查询优化：当日存在相同created值时仅返回单条最新记录

开发报表系统时，需要通过SQL查询返回多日内每日仅一条最新记录，避免报表重复。当前用INNER JOIN分组的方式能拿到每日最新记录，但当同一天存在两条created时间完全相同的记录时，会返回两条重复数据，需要修改查询确保每日仅返回一条。

原查询语句 #

SELECT
    *
FROM
    tlp_consolidated_reports AS ConsolidatedReport
INNER JOIN
(
    SELECT
        DATE(created) AS created_at,
        MAX(created) AS max_created_at
    FROM
        tlp_consolidated_reports
    WHERE
        type = 'commissions'
        AND
        user_id = '33'
        AND
        report_from >= '2022-08-01 00:00:00'
        AND
        report_to <= '2022-08-31 23:59:59'
    GROUP BY DATE(created)
) AS joinedReport
  ON joinedReport.created_at = DATE(ConsolidatedReport.created)
  AND
  joinedReport.max_created_at = ConsolidatedReport.created
WHERE
    ConsolidatedReport.type = 'commissions'
    AND
    ConsolidatedReport.user_id = '33'
    AND
    ConsolidatedReport.report_from >= '2022-08-01 00:00:00'
    AND
    ConsolidatedReport.report_to <= '2022-08-31 23:59:59'
ORDER BY
    ConsolidatedReport.created DESC

示例数据 #

2022-08-09 21:00:00
2022-08-08 15:00:00
2022-08-07 14:00:00
2022-08-07 14:00:00
2022-08-07 13:00:00

当前查询结果（存在重复） #

2022-08-09 21:00:00
2022-08-07 14:00:00
2022-08-07 14:00:00

方法1：使用窗口函数ROW_NUMBER()（推荐，通用型） #

利用窗口函数按日期分组，给同日期的记录按created降序（可额外加主键确保唯一排序）分配行号，只保留行号为1的记录，完美解决同时间重复问题。

修改后的SQL：

SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY DATE(created) 
            ORDER BY created DESC, id DESC -- 加id确保同时间时取唯一一条，id是表主键
        ) AS row_num
    FROM tlp_consolidated_reports
    WHERE
        type = 'commissions'
        AND user_id = '33'
        AND report_from >= '2022-08-01 00:00:00'
        AND report_to <= '2022-08-31 23:59:59'
) AS temp
WHERE row_num = 1
ORDER BY created DESC;

逻辑说明：

• PARTITION BY DATE(created)：按日期分组
• ORDER BY created DESC, id DESC：同日期内先按创建时间降序，时间相同时按主键降序（保证排序唯一）
• 外层过滤row_num=1：只取每组的第一条记录

方法2：主查询加GROUP BY合并同时间记录（适合MySQL） #

如果使用MySQL，可以在主查询中通过GROUP BY DATE(created), created合并同日期同时间的记录，用ANY_VALUE()获取其他字段的值：

SELECT
    ANY_VALUE(ConsolidatedReport.*)
FROM
    tlp_consolidated_reports AS ConsolidatedReport
INNER JOIN
(
    SELECT
        DATE(created) AS created_at,
        MAX(created) AS max_created_at
    FROM
        tlp_consolidated_reports
    WHERE
        type = 'commissions'
        AND user_id = '33'
        AND report_from >= '2022-08-01 00:00:00'
        AND report_to <= '2022-08-31 23:59:59'
    GROUP BY DATE(created)
) AS joinedReport
  ON joinedReport.created_at = DATE(ConsolidatedReport.created)
  AND joinedReport.max_created_at = ConsolidatedReport.created
WHERE
    ConsolidatedReport.type = 'commissions'
    AND ConsolidatedReport.user_id = '33'
    AND ConsolidatedReport.report_from >= '2022-08-01 00:00:00'
    AND ConsolidatedReport.report_to <= '2022-08-31 23:59:59'
GROUP BY DATE(ConsolidatedReport.created), ConsolidatedReport.created
ORDER BY
    ConsolidatedReport.created DESC;

逻辑说明：

• GROUP BY DATE(created), created：把同日期同时间的记录合并成一组
• ANY_VALUE()：从每组中任意取一条记录的字段值（如果需要指定取某条，可替换成对应聚合函数）

方法3：使用DISTINCT ON（适合PostgreSQL） #

PostgreSQL支持DISTINCT ON语法，可以直接指定按日期去重，保留每组的第一条记录：

SELECT DISTINCT ON (DATE(created)) *
FROM tlp_consolidated_reports
WHERE
    type = 'commissions'
    AND user_id = '33'
    AND report_from >= '2022-08-01 00:00:00'
    AND report_to <= '2022-08-31 23:59:59'
ORDER BY DATE(created), created DESC, id DESC;

逻辑说明：

• DISTINCT ON (DATE(created))：按日期去重，每组只保留第一条
• ORDER BY必须以DISTINCT ON的字段开头，后续指定排序规则确保取最新的那条

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