如何查询各公司vat与witholding_tax两类付款的当年最新记录

我有两张数据表：

• Companies（公司表）
• company_payments（公司付款记录表）

每家公司会产生vat和witholding_tax两类付款，我当前使用的如下SQL只能返回每家公司本年度的最新一条付款记录：

SELECT * FROM companies c 
JOIN ( SELECT MAX(id) max_id, company_id FROM company_payments ) 
c_max ON (c_max.company_id = c.id)
JOIN company_payments cp ON (cp.id = c_max.max_id)
WHERE 
YEAR(cp.last_payment) = YEAR(CURDATE())

现在需要调整查询逻辑，针对每家公司分别返回vat和witholding_tax两类付款的本年度最新记录，若某类付款无对应记录则返回空记录，请问该如何实现该需求？

方案1：窗口函数实现（适用于MySQL 8.0+、PostgreSQL等支持窗口函数的数据库） #

思路是先生成「所有公司+两类付款类型」的基础行，保证无付款记录的分类也会留空返回，再通过窗口函数分组取最新记录：

WITH type_list AS (
    -- 固定要查询的两类付款类型
    SELECT 'vat' AS payment_type
    UNION ALL
    SELECT 'witholding_tax' AS payment_type
),
ranked_payments AS (
    SELECT
        c.id AS company_id,
        c.*, -- 可替换为你需要的公司表具体字段
        tl.payment_type,
        cp.*, -- 可替换为你需要的付款表具体字段
        -- 按公司+付款类型分组，最新记录排第一，业务上如果按付款时间判断最新，可改为ORDER BY cp.last_payment DESC
        ROW_NUMBER() OVER (PARTITION BY c.id, tl.payment_type ORDER BY cp.id DESC) AS rn
    FROM companies c
    -- 交叉关联付款类型，生成每家公司对应两类付款的基础行
    CROSS JOIN type_list tl
    -- 左关联本年度对应类型的付款记录，无匹配则付款字段为空
    LEFT JOIN company_payments cp 
        ON cp.company_id = c.id
        AND cp.payment_type = tl.payment_type
        AND YEAR(cp.last_payment) = YEAR(CURDATE())
)
-- 取每个分组的最新一条记录
SELECT * FROM ranked_payments WHERE rn = 1;

方案2：兼容低版本MySQL（5.x及以下不支持窗口函数场景） #

用关联子查询先拿到每个公司+付款类型的最新记录ID，再关联查询：

SELECT
    c.id AS company_id,
    c.*,
    tl.payment_type,
    cp.*
FROM companies c
CROSS JOIN (
    SELECT 'vat' AS payment_type
    UNION ALL
    SELECT 'witholding_tax' AS payment_type
) tl
-- 先聚合拿到本年每个公司+付款类型的最新付款ID
LEFT JOIN (
    SELECT MAX(id) AS max_id, company_id, payment_type
    FROM company_payments
    WHERE YEAR(last_payment) = YEAR(CURDATE())
    GROUP BY company_id, payment_type
) c_max ON c_max.company_id = c.id AND c_max.payment_type = tl.payment_type
-- 关联拿到完整付款记录
LEFT JOIN company_payments cp ON cp.id = c_max.max_id;

注意：如果你的付款类型字段名不是payment_type，请替换为表中实际对应的字段名即可。

内容的提问来源于stack exchange，提问作者stackoverflow account