查询结果含多列时仅保留重复数据中日期最新的行

现有SQL能否实现需求？ #

• 第一个SQL仅做全表查询，完全未处理重复数据，无法实现需求，会返回所有行（包括重复的收据信息）。
• 第二个SQL存在两处关键问题，同样无法实现需求：
  1. 字段别名错误：MAX("CusName") AS "CusDate" 里的别名应为CusName，而非CusDate；
  2. 聚合逻辑错误：MAX("CusName")和MAX("RecNum")取的是字段的最大值，而非与最新RecDate对应的那一行数据。比如同一个InvNum下如果有不同客户名称，MAX(CusName)会取字典序最大的名称，而非最新收据对应的客户名；如果RecNum不是按时间递增编号的，MAX(RecNum)也不会是最新收据的编号。

需要进行哪些调整？ #

推荐两种调整方案：

方案一：使用窗口函数（分区方式，最稳妥） #

通过ROW_NUMBER()窗口函数按InvNum分区，按RecDate倒序排序，取每个分区的第一行（即最新日期的行）：

SELECT "InvNum", "CusName", "RecNum", "RecDate"
FROM (
    SELECT 
        "InvNum", "CusName", "RecNum", "RecDate",
        -- 按InvNum分组，按RecDate倒序排，为每行分配行号
        ROW_NUMBER() OVER (PARTITION BY "InvNum" ORDER BY "RecDate" DESC) AS rn
    FROM CUS_PAY
    WHERE "RecNum" IS NOT NULL
) t
-- 只保留每个InvNum下最新日期的行
WHERE rn = 1

如果同一个InvNum下存在多个相同的最新RecDate，可以在ORDER BY后追加第二个排序字段（比如RecNum DESC）确保只返回一行：

ROW_NUMBER() OVER (PARTITION BY "InvNum" ORDER BY "RecDate" DESC, "RecNum" DESC) AS rn

方案二：使用关联子查询（无需分区） #

先查询每个InvNum对应的最大RecDate，再关联原表获取对应行的数据：

SELECT cp."InvNum", cp."CusName", cp."RecNum", cp."RecDate"
FROM CUS_PAY cp
INNER JOIN (
    SELECT "InvNum", MAX("RecDate") AS max_rec_date
    FROM CUS_PAY
    WHERE "RecNum" IS NOT NULL
    GROUP BY "InvNum"
) t ON cp."InvNum" = t."InvNum" AND cp."RecDate" = t.max_rec_date
WHERE cp."RecNum" IS NOT NULL

注意：如果同一个InvNum下有多个行的RecDate等于最大日期，这个查询会返回所有这些行，需额外处理（比如再聚合RecNum）。

是否必须使用分区？ #

不是必须使用分区（窗口函数的分区语法），但分区方式是最简洁、可读性最高且不易出错的方案。不用分区的话可以用关联子查询，但处理边缘情况（如同一InvNum下多个最新日期行）时逻辑更繁琐。

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