如何基于子列求和筛选存在待缴发票付款的用户

核心判定逻辑 #

待缴发票的判定规则如下：

• 单张发票总应付金额：对该发票关联的所有invoice_items记录的price字段求和
• 单张发票已付总金额：对该发票关联的所有payments记录的amount字段求和
• 若单张发票满足「总应付金额 > 已付总金额」，即判定为存在待缴余额，对应归属用户为目标查询结果

现有数据表结构 #

diplomas -> id, name
users -> id, name, email
diploma_user -> id, user_id, diploma_id, invoice_id (many to many relation)
invoices -> id, user_id
invoice_items -> id, invoice_id, item_type, item_id, price
payments -> id, invoice_id, amount

实现SQL #

基于表结构中invoices表直接存储user_id关联用户的设计，可直接使用以下语句查询：

SELECT DISTINCT u.id, u.name, u.email
FROM users u
-- 关联用户名下所有发票
INNER JOIN invoices i 
    ON u.id = i.user_id
-- 聚合计算每张发票的总应付金额
LEFT JOIN (
    SELECT 
        invoice_id,
        SUM(price) AS total_payable
    FROM invoice_items
    GROUP BY invoice_id
) item_stat 
    ON i.id = item_stat.invoice_id
-- 聚合计算每张发票的累计已付金额
LEFT JOIN (
    SELECT 
        invoice_id,
        SUM(amount) AS total_paid
    FROM payments
    GROUP BY invoice_id
) pay_stat 
    ON i.id = pay_stat.invoice_id
-- 筛选存在待缴差额的发票
WHERE 
    -- 用COALESCE处理无付款记录、无明细的NULL值场景
    COALESCE(item_stat.total_payable, 0) > COALESCE(pay_stat.total_paid, 0)

适配调整说明 #

如果业务中发票归属是通过diploma_user中间表关联用户，而非invoices表直接存储user_id，只需将用户与发票的关联逻辑替换为以下写法，其余聚合、筛选逻辑保持不变即可：

SELECT DISTINCT u.id, u.name, u.email
FROM users u
-- 通过中间表关联用户和对应发票
INNER JOIN diploma_user du 
    ON u.id = du.user_id
INNER JOIN invoices i 
    ON du.invoice_id = i.id
-- 后续聚合、筛选逻辑和上述SQL一致

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