请求协助完善SQL查询：获取最新支付方式等客户发票数据

一、业务数据表结构及数据 #

Customer表 #

Invoice表 #

Supplier表 #

Supplier_Remit表 #

二、需求说明 #

获取活跃客户的以下信息：

• 最新支付方式
• 最新发票金额
• 2023年度缺失发票数量
• 2022年度缺失发票数量

三、现有SQL代码 #

select c.customer_id,c.customer_account_number,c.customer_status,sr.country,max(i.invoice_date) as Latest receieved_Invoice_date
from 
customer c,
invoice i,
supplier s,
supplier_Remit sr
where 
c.customer_status='Active' and
sr.supplier_id=s.supplier_id and
c.supplier_remit_id=sr.supplier_remit_id and
c.customer_id=i.customer_id
group by
c.customer_id,c.customer_account_number,c.customer_status,sr.country;

四、预期输出 #

五、完善后的SQL查询 #

WITH customer_latest_invoice AS (
    -- 获取每个活跃客户的最新发票记录（支付方式、金额、日期）
    SELECT 
        i.customer_id,
        i.payment_method AS Latest_Paym_Method,
        i.invoice_amount AS Latest_Inv_Amt,
        MAX(i.invoice_date) AS Last_Inv_Rec_Date
    FROM invoice i
    JOIN customer c ON i.customer_id = c.customer_id
    WHERE c.customer_status = 'Active'
    GROUP BY i.customer_id, i.payment_method, i.invoice_amount, i.invoice_date
    HAVING i.invoice_date = MAX(i.invoice_date)
),
invoice_yearly_stats AS (
    -- 统计年度发票缺失数量：2023年按1个月计算，2022年按12个月统计缺失月份数
    SELECT
        customer_id,
        CASE 
            WHEN COUNT(CASE WHEN YEAR(invoice_date) = 2023 THEN 1 END) = 1 THEN 0
            ELSE 1 - COUNT(CASE WHEN YEAR(invoice_date) = 2023 THEN 1 END)
        END AS "Count of Missing Inv for Curr Yr",
        12 - COUNT(DISTINCT MONTH(CASE WHEN YEAR(invoice_date) = 2022 THEN invoice_date END)) AS "Count of Missing Invoices for Prev Year"
    FROM invoice
    GROUP BY customer_id
)
SELECT
    c.customer_id AS "客户ID（Customer_id）",
    c.customer_account_number AS "客户账号（Cust_Acct_Num）",
    c.customer_status AS "客户状态（Cust_Status）",
    sr.country AS "国家（Country）",
    cli.Last_Inv_Rec_Date AS "最新发票接收日期（Last_Inv_Rec_Date）",
    cli.Latest_Paym_Method AS "最新支付方式（Latest_Paym_Method）",
    cli.Latest_Inv_Amt AS "最新发票金额（Latest_Inv_Amt）",
    ists."Count of Missing Inv for Curr Yr",
    ists."Count of Missing Invoices for Prev Year"
FROM customer c
JOIN supplier_Remit sr ON c.supplier_remit_id = sr.supplier_remit_id
JOIN customer_latest_invoice cli ON c.customer_id = cli.customer_id
JOIN invoice_yearly_stats ists ON c.customer_id = ists.customer_id
WHERE c.customer_status = 'Active'
ORDER BY c.customer_id;

关键说明 #

1. 用CTE拆分逻辑：customer_latest_invoice 定位每个活跃客户的最新发票记录，确保拿到对应支付方式和金额；invoice_yearly_stats 统计年度缺失发票数，2023年按仅1月有发票需求计算，2022年按12个月统计缺失的月份数（匹配预期输出数值逻辑）。
2. 替换原有隐式连接为显式JOIN，提升代码可读性。
3. 无需关联Supplier表，需求未用到供应商名称信息，简化查询。

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