如何获取指定供应商的最新付款支票（含名称与TaxID）

问题描述 #

我希望获取指定供应商的最新创建的付款支票，同时显示供应商名称与TaxID。当前使用如下SQL查询仅能返回单个供应商的结果：

select distinct top 200 cl.ClaimNumber , con.Name, ch.CreateTime,con.TaxID from cc_claim cl 
 join cc_claimcontact ccon on ccon.ClaimID = cl.ID
 join cc_check ch on ch.ClaimID = cl.ID
 join cc_contact con on ccon.ContactID = con.ID
 where 
 ch.PayTo in  ('WYATT LAW FIRM, LTD','Liberty Mutual')
 and
 con.TaxID in('0001682-4774-2861000','0004291-4004-1924000')
 order by ch.CreateTime desc

我需要调整查询，使其针对输入的所有供应商，各返回一条最新支票的记录（仅首行）。

解决方案 #

方法1：CTE+窗口函数（兼容多数数据库） #

通过ROW_NUMBER()窗口函数对每个供应商分组排序，精准获取每组最新的一条记录：

WITH RankedChecks AS (
    SELECT 
        cl.ClaimNumber,
        con.Name,
        ch.CreateTime,
        con.TaxID,
        -- 按供应商唯一标识分组，按创建时间倒序排名
        ROW_NUMBER() OVER (PARTITION BY con.Name, con.TaxID ORDER BY ch.CreateTime DESC) AS rn
    FROM cc_claim cl 
    JOIN cc_claimcontact ccon ON ccon.ClaimID = cl.ID
    JOIN cc_check ch ON ch.ClaimID = cl.ID
    JOIN cc_contact con ON ccon.ContactID = con.ID
    WHERE 
        ch.PayTo IN ('WYATT LAW FIRM, LTD','Liberty Mutual')
        AND con.TaxID IN('0001682-4774-2861000','0004291-4004-1924000')
)
SELECT ClaimNumber, Name, CreateTime, TaxID
FROM RankedChecks
WHERE rn = 1;

核心逻辑：

• PARTITION BY con.Name, con.TaxID：按供应商的名称+TaxID分组，确保每个供应商独立计算排名
• ORDER BY ch.CreateTime DESC：每组内按支票创建时间倒序排列，最新记录排名为1
• 外层筛选rn=1，直接得到每个供应商的最新支票数据

方法2：TOP 1 WITH TIES（SQL Server专属） #

如果使用SQL Server，可以用更简洁的写法实现相同效果：

SELECT TOP 1 WITH TIES
    cl.ClaimNumber,
    con.Name,
    ch.CreateTime,
    con.TaxID
FROM cc_claim cl 
JOIN cc_claimcontact ccon ON ccon.ClaimID = cl.ID
JOIN cc_check ch ON ch.ClaimID = cl.ID
JOIN cc_contact con ON ccon.ContactID = con.ID
WHERE 
    ch.PayTo IN ('WYATT LAW FIRM, LTD','Liberty Mutual')
    AND con.TaxID IN('0001682-4774-2861000','0004291-4004-1924000')
ORDER BY ROW_NUMBER() OVER (PARTITION BY con.Name, con.TaxID ORDER BY ch.CreateTime DESC);

TOP 1 WITH TIES会自动返回所有排名为1的记录，恰好对应每个供应商的最新支票。

内容的提问来源于stack exchange，提问作者Amit Rajput