如何使用TSQL获取客户的最新发票编号及发票日期

嗨，我来帮你搞定这个需求！你要的是每个客户对应的最新发票编号和日期，结合你给出的表结构，这里有两种实用的TSQL实现方法，咱们一步步来看：

方法一：使用窗口函数（推荐） #

这种方法逻辑清晰，还能轻松处理同一客户同一天有多张发票的情况（比如取编号最大的那张），用ROW_NUMBER()窗口函数就能实现：

SELECT
    c.customer_id,
    c.customer_name,
    i.invoice_number AS most_recent_invoice_number,
    i.invoice_date AS most_recent_invoice_date
FROM
    customer AS c
JOIN (
    SELECT
        customer_id,
        invoice_number,
        invoice_date,
        -- 按客户分组，按发票日期倒序、发票编号倒序排序，最新的标记为1
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY invoice_date DESC, invoice_number DESC) AS rn
    FROM invoice
) AS i ON i.customer_id = c.customer_id AND i.rn = 1

逻辑说明： #

• 子查询里用PARTITION BY customer_id把发票按客户分组
• ORDER BY invoice_date DESC, invoice_number DESC确保每个客户组里，最新日期的发票排在最前面；如果同一天有多个发票，就取编号最大的那个
• 最后只筛选出行号rn=1的记录，也就是每个客户的最新发票

方法二：使用GROUP BY + 关联子查询 #

如果你习惯用传统的分组方式，也可以先找出每个客户的最大发票日期，再关联回发票表拿到对应的发票编号：

SELECT
    c.customer_id,
    c.customer_name,
    i.invoice_number AS most_recent_invoice_number,
    i.invoice_date AS most_recent_invoice_date
FROM
    customer AS c
JOIN invoice AS i ON i.customer_id = c.customer_id
JOIN (
    -- 先获取每个客户的最新发票日期
    SELECT customer_id, MAX(invoice_date) AS max_invoice_date
    FROM invoice
    GROUP BY customer_id
) AS max_dates ON max_dates.customer_id = c.customer_id AND max_dates.max_invoice_date = i.invoice_date

补充处理： #

如果你的业务场景中，同一客户在同一天可能有多个发票，上面的查询会返回多条该客户的记录。这时候可以把外层查询改成聚合形式，确保只返回一条最新记录：

SELECT
    c.customer_id,
    c.customer_name,
    MAX(i.invoice_number) AS most_recent_invoice_number,
    max_dates.max_invoice_date AS most_recent_invoice_date
FROM
    customer AS c
JOIN invoice AS i ON i.customer_id = c.customer_id
JOIN (
    SELECT customer_id, MAX(invoice_date) AS max_invoice_date
    FROM invoice
    GROUP BY customer_id
) AS max_dates ON max_dates.customer_id = c.customer_id AND max_dates.max_invoice_date = i.invoice_date
GROUP BY c.customer_id, c.customer_name, max_dates.max_invoice_date

小提示： #

为了让查询更高效，建议给invoice表的customer_id和invoice_date字段建立联合索引，这样分组和排序的时候速度会快很多哦！

备注：内容来源于stack exchange，提问作者cspell