SQL Query：获取最新付款方式、发票日期及缺失数量

结论

可以用 ROW_NUMBER() 找出每位客户最新的一张发票，再通过条件聚合，统计各年份已经收到发票的月份数。

根据期望结果，“缺失发票数量”的计算包含两个前提：

• 每位客户每月应收到一张发票。
• 统计日期为 2023 年 1 月，因此 2023 年截至当时应收到 1 张发票，2022 年全年应收到 12 张。

在 SQL Server 中，可以这样写：

DECLARE @AsOfDate date = '2023-01-31';

WITH LatestInvoice AS
(
    SELECT
        i.Customer_id,
        i.Invoice_Date,
        i.Invoice_Amount,
        i.[Payment Method],
        i.Invoice_Number,
        ROW_NUMBER() OVER
        (
            PARTITION BY i.Customer_id
            ORDER BY i.Invoice_Date DESC, i.Invoice_Number DESC
        ) AS rn
    FROM Invoice AS i
    WHERE i.Invoice_Date <= @AsOfDate
),
InvoiceCounts AS
(
    SELECT
        i.Customer_id,

        COUNT(DISTINCT CASE
            WHEN i.Invoice_Date >= DATEFROMPARTS(YEAR(@AsOfDate), 1, 1)
             AND i.Invoice_Date <= @AsOfDate
            THEN MONTH(i.Invoice_Date)
        END) AS CurrentYearMonthsReceived,

        COUNT(DISTINCT CASE
            WHEN i.Invoice_Date >= DATEFROMPARTS(YEAR(@AsOfDate) - 1, 1, 1)
             AND i.Invoice_Date <  DATEFROMPARTS(YEAR(@AsOfDate), 1, 1)
            THEN MONTH(i.Invoice_Date)
        END) AS PreviousYearMonthsReceived
    FROM Invoice AS i
    GROUP BY i.Customer_id
)
SELECT
    c.Customer_id,
    c.Customer_Account_number AS Cust_Acct_Num,
    c.Customer_Status AS Cust_Status,
    sr.Country,
    li.Invoice_Date AS Last_Inv_Rec_Date,
    li.[Payment Method] AS Latest_Paym_Method,
    li.Invoice_Amount AS Latest_Inv_Amt,

    CASE
        WHEN MONTH(@AsOfDate) - COALESCE(ic.CurrentYearMonthsReceived, 0) < 0
            THEN 0
        ELSE MONTH(@AsOfDate) - COALESCE(ic.CurrentYearMonthsReceived, 0)
    END AS Missing_Inv_Current_Year,

    CASE
        WHEN 12 - COALESCE(ic.PreviousYearMonthsReceived, 0) < 0
            THEN 0
        ELSE 12 - COALESCE(ic.PreviousYearMonthsReceived, 0)
    END AS Missing_Inv_Previous_Year
FROM Customer AS c
INNER JOIN Supplier_Remit AS sr
    ON sr.Supplier_Remit_id = c.Supplier_Remit_id
   AND sr.Supplier_id = c.Supplier_id
LEFT JOIN LatestInvoice AS li
    ON li.Customer_id = c.Customer_id
   AND li.rn = 1
LEFT JOIN InvoiceCounts AS ic
    ON ic.Customer_id = c.Customer_id
WHERE c.Customer_Status = 'Active'
ORDER BY c.Customer_id;

为什么不能只用 MAX(Invoice_Date)

MAX(i.Invoice_Date) 只能得到最新日期，无法保证同一行中的 Payment Method 和 Invoice_Amount 也来自这张最新发票。

例如，下面的写法并不可靠：

SELECT
    Customer_id,
    MAX(Invoice_Date),
    MAX(Invoice_Amount)
FROM Invoice
GROUP BY Customer_id;

最大金额可能来自另一张历史发票，并不一定属于日期最新的发票。ROW_NUMBER() 会在每位客户的发票中按日期倒序排列，再用 rn = 1 取出最新发票的完整记录。

如果同一天有多张发票，查询会将 Invoice_Number DESC 作为第二个排序条件，把发票号较大的记录视为最新记录。如果表中有创建时间或更新时间字段，应优先使用该字段处理日期并列的情况。

缺失数量如何计算

当前年份应收到发票的月份数由统计日期决定：

MONTH(@AsOfDate)

当 @AsOfDate = '2023-01-31' 时，结果为 1。计算方式如下：

2023 年缺失数 = 1 - 2023 年已收到发票的月份数
2022 年缺失数 = 12 - 2022 年已收到发票的月份数

根据示例数据，结果为：

客户 4 在 2022 年 10 月有两张发票。如果业务规则是“每月应有一张发票”，就应按不同月份统计。这两张发票只能算作收到 1 个月，缺失数应为 11。如果按发票总张数统计，缺失数才是 10。题目给出的期望值采用的是按发票张数计算的口径。

如果必须严格得到题目中的 10、11、10，需要把 InvoiceCounts 中的两个表达式：

COUNT(DISTINCT CASE ... THEN MONTH(i.Invoice_Date) END)

改为：

COUNT(CASE ... THEN 1 END)

例如，上一年度可以写成：

COUNT(CASE
    WHEN i.Invoice_Date >= DATEFROMPARTS(YEAR(@AsOfDate) - 1, 1, 1)
     AND i.Invoice_Date <  DATEFROMPARTS(YEAR(@AsOfDate), 1, 1)
    THEN 1
END) AS PreviousYearInvoicesReceived

究竟按月份还是按发票张数计算，要看业务如何定义“缺失发票”。如果每个月可能有多张发票，按月份统计更符合“每月应收一张”的含义。

其他注意事项

Invoice_Date 应使用 date 或 datetime 类型，不要保存为字符串。日期常量建议使用含义明确的 ISO 格式：

'2023-01-31'

查询通过 LEFT JOIN 连接发票数据，所以没有任何发票的 Active 客户仍会出现在结果中。其最新发票字段为 NULL，缺失数量则按尚未收到任何发票计算。

当前查询没有使用 Supplier 表中的字段，因此不需要连接该表。只有需要返回 Supplier_Name 等信息时，才需要添加：

INNER JOIN Supplier AS s
    ON s.Supplier_id = c.Supplier_id

备注：内容仅供参考。