如何使用相关子查询获取供应商未来两周内到期的最早发票并完善invoice_due_date字段？

让我帮你修正并完善这个SQL查询，解决你卡在invoice_due_date处理上的问题。你的需求是获取每个供应商未来两周内到期的最早发票，返回指定的五列，我们先梳理下现有代码的问题，再给出正确的实现：

现有代码的核心问题 #

• JOIN语句缺少ON关联条件，会导致不必要的笛卡尔积
• 子查询里的关联条件错误（v.vendor_id = v.vendor_id是无效的自关联，应该关联发票和供应商的vendor_id）
• 没有过滤“未来两周内到期”的范围条件
• 分组逻辑不符合需求：你需要每个供应商一行数据，而不是按发票字段分组

方案一：使用相关子查询实现（符合你的需求描述） #

这是最贴合你提到的“相关子查询”的方式，直接针对每个供应商找到其未来两周内最早到期的发票：

SELECT 
    v.vendor_name,
    i.invoice_number,
    i.invoice_date,
    i.invoice_due_date,
    i.invoice_total
FROM vendors v
JOIN invoices i ON v.vendor_id = i.vendor_id
WHERE 
    -- 筛选未来两周内到期的发票（当前日期到14天后）
    i.invoice_due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY)
    -- 相关子查询：锁定当前供应商在该范围内的最早到期日期
    AND i.invoice_due_date = (
        SELECT MIN(invoice_due_date)
        FROM invoices
        WHERE vendor_id = v.vendor_id
          AND invoice_due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY)
    );

逻辑说明

1. 用JOIN正确关联供应商和发票表，关联条件是两者的vendor_id匹配
2. 第一个WHERE条件过滤出到期日在当前日期到未来14天内的发票
3. 相关子查询针对每个供应商，找到该供应商符合时间范围的最小invoice_due_date，也就是最早到期的发票
4. 最终每个供应商只会返回一行对应其最早到期的符合条件的发票

方案二：使用窗口函数（更灵活，适合复杂排序场景） #

如果你的数据库支持窗口函数（比如MySQL 8+、PostgreSQL、SQL Server等），用ROW_NUMBER()可以更清晰地处理排序和去重：

WITH ranked_invoices AS (
    SELECT 
        v.vendor_name,
        i.invoice_number,
        i.invoice_date,
        i.invoice_due_date,
        i.invoice_total,
        -- 按供应商分组，按到期日期升序排名，最早的发票排第1
        ROW_NUMBER() OVER (PARTITION BY v.vendor_id ORDER BY i.invoice_due_date ASC) AS rn
    FROM vendors v
    JOIN invoices i ON v.vendor_id = i.vendor_id
    WHERE i.invoice_due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY)
)
SELECT 
    vendor_name,
    invoice_number,
    invoice_date,
    invoice_due_date,
    invoice_total
FROM ranked_invoices
WHERE rn = 1;

逻辑说明

1. 用CTE（公共表表达式）先给每个供应商的符合条件的发票按到期日期排序，rn=1就是该供应商最早到期的发票
2. 最后从CTE中筛选排名为1的记录，确保每个供应商只返回一行
3. 如果有多个发票到期日相同且都是最早的，这个方案只会返回其中一行（如果需要返回所有相同最早的，把ROW_NUMBER()换成RANK()即可）

注意事项 #

• 如果你的invoice_due_date字段包含时间部分（比如YYYY-MM-DD HH:MM:SS），可以把CURDATE()换成NOW()，确保时间范围的准确性
• 若数据库不支持CTE（比如MySQL 5.x），可以把CTE改写为子查询的形式

内容的提问来源于stack exchange，提问作者Diana Brooks