MySQL加入3计算结果

deiga 发表于 Dev

迪加

我想加入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';

哈里德·朱奈德（M Khalid Junaid）

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

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;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。