mysql - 如何根据其他联接表条件选择某些表字段的总和？

Question

我一直在研究费用跟踪系统。我有三个主要表格：“费用”、“费用报告”、“付款”。

我这样构建我的数据库：

• “费用”属于一个“费用报告”
• “expense_reports”有很多“费用”
• 'expense_reports' 属于许多 'payments'
• 'payments' 属于许多 'expense_reports'

我为“expense_reports”和“payments”表添加了一个数据透视表，即“expense_report_payment”，以处理多对多关系。'expense_report_payment' 表有 ff 字段：

• ID
• 费用报告ID
• 付款ID
• 支付

这是我获取费用和付款总额的查询：

SELECT 
  IFNULL(SUM(ex.`amount`), 0) AS total_expenses,
  IFNULL(SUM(erp.`payment`), 0) AS total_payment 
FROM
  `expenses` ex 
  JOIN `expense_reports` er 
    ON er.`id` = ex.`expense_report_id` 
  LEFT JOIN `expense_report_payment` erp 
    ON erp.`expense_report_id` = er.`id` 
  JOIN `payments` p 
    ON p.`id` = erp.`payment_id` 
    AND p.`deleted_at` IS NULL 
    AND p.`cancelled_at` IS NULL 
    AND p.`received_at` IS NOT NULL 
WHERE ex.`deleted_at` IS NULL 
  AND er.`deleted_at` IS NULL 
  AND er.`rejected_at` IS NULL 
  AND er.`cancelled_at` IS NULL 

如果收到付款（total_expenses = 100；total_payment = 100），我会得到总价值。但是，如果尚未收到付款，则费用和付款的总金额为零。

标签： mysqlsqlmariadb

Answer 1

LEFT JOIN表。通常，您还应该将WHERE右连接表上的条件移至LEFT JOINs ON子句。尝试

SELECT 
  IFNULL(SUM(ex.`amount`), 0) AS total_expenses,
  IFNULL(SUM(erp.`payment`), 0) AS total_payment 
FROM
  `expenses` ex 
  LEFT JOIN `expense_reports` er 
    ON er.`id` = ex.`expense_report_id` 
      AND er.`deleted_at` IS NULL 
      AND er.`rejected_at` IS NULL 
      AND er.`cancelled_at` IS NULL    
  LEFT JOIN `expense_report_payment` erp 
    ON erp.`expense_report_id` = er.`id` 
  LEFT JOIN `payments` p 
    ON p.`id` = erp.`payment_id` 
    AND p.`deleted_at` IS NULL 
    AND p.`cancelled_at` IS NULL 
    AND p.`received_at` IS NOT NULL 
WHERE ex.`deleted_at` IS NULL