MySQL join 3计算结果

Question

我有 3 个不同的 MySQL 计算，我想加入。我需要能够显示某些列可能不存在总和的行，或者某些发票可能没有公司 ID。

我想得到类似的东西:

passport_amount | invoice_amount | balance_amount | corporation_id
------------------------------------------------------------------
           345  |       2345     |          56    |         56

这样我就可以在我的应用程序代码中处理这些值，方法是迭代列表一次而不是从数据库中获取数据三次，然后迭代三次以组合这些值。

SELECT 
  sum(passports.amount) AS passports_amount, 
  companies.corporation_id 
FROM 
  passports
INNER JOIN 
  employees ON ( passports.employee_id = employees.id ) 
INNER JOIN 
  companies ON ( employees.company_id = companies.id ) 
WHERE 
  ((((passports.pass_type IN ('sport','culture','both')) 
    AND 
      (MONTH(passports.valid_from) >= 1 
      AND MONTH(passports.valid_from) <= 9 
      AND YEAR(passports.valid_from) = year(now()))) 
    AND (passports.removed = 0 
      AND passports.valid_from <= date('2014-09-29 11:55:26'))) 
  AND (companies.removed = 0) 
  AND companies.corporation_id IS NOT NULL) 
GROUP BY 
  companies.corporation_id;


SELECT 
  sum(invoices.amount) AS invoices_amount, 
  invoices.corporation_id 
FROM 
  invoices 
WHERE 
  ((((YEAR(sent_at) = 2014) 
    AND (invoices.product_type_id IN (2,3,4))) 
  AND 
    (invoices.removed = 0 
    AND invoices.activated = 1)) 
  AND invoices.corporation_id IS NOT NULL) 
GROUP BY 
  invoices.corporation_id;



SELECT 
  amount AS balance_amount, 
  business_id AS corporation_id 
FROM 
  invoice_balances 
WHERE 
  business_type = 'Corporation';

Answer 1

您可以在左连接中使用子选择来组合余额和发票金额的查询，但这看起来很奇怪并且在性能方面可能很昂贵

SELECT 
  SUM(p.amount) AS passports_amount, 
  ii.invoices_amount,
  b.balance_amount,
  c.corporation_id 
FROM 
  passports p
INNER JOIN employees e ON ( p.e = e.id ) 
INNER JOIN companies c ON ( e.company_id = c.id ) 
/* Added left join for balance using subselect*/
LEFT JOIN (
SELECT amount AS balance_amount,   business_id AS corporation_id 
FROM   invoice_balances 
WHERE   business_type = 'Corporation'
) b ON (c.corporation_id  = b.corporation_id)
/* Added left join for invoices_amount using subselect*/
LEFT JOIN(
SELECT SUM(i.amount) AS invoices_amount, 
  i.corporation_id 
FROM 
  invoices i
WHERE 
  ((((YEAR(sent_at) = 2014) 
    AND (i.product_type_id IN (2,3,4))) 
  AND (i.removed = 0     AND i.activated = 1)
  ) 
  AND i.corporation_id IS NOT NULL) 
GROUP BY i.corporation_id
) ii ON(c.corporation_id = ii.corporation_id)
/* end of joins */
WHERE 
  ((((p.pass_type IN ('sport','culture','both')) 
    AND 
      (MONTH(p.valid_from) >= 1 
      AND MONTH(p.valid_from) <= 9 
      AND YEAR(p.valid_from) = YEAR(NOW()))) 
    AND (p.removed = 0 
      AND p.valid_from <= DATE('2014-09-29 11:55:26'))) 
  AND (c.removed = 0) 
  AND c.corporation_id IS NOT NULL) 
GROUP BY c.corporation_id;