mysql - 这个选择子查询可以避免吗？

Question

我在 mysql 中有两个表(发票和税):

Invoices:
  - id
  - account_id
  - issued_at
  - total
  - gross_amount
  - country

Taxes:
  - id
  - invoice_id
  - tax_name
  - tax_rate
  - taxable_amount
  - tax_amount

我正在尝试检索这样的报告

rep_month  | country | total_amount | tax_name | tax_rate(%) | taxable_amount | tax_amount
--------------------------------------------------------------------------------------
2017-01-01 | ES      | 1000         | TAX1     | 21          | 700            | 147
2017-01-01 | ES      | 1000         | TAX2     | -15         | 700            | 105
2016-12-01 | FR      | 100          | TAX4     | 20          | 30             | 6
2016-12-01 | FR      | 100          | B2B      | 0           | 70             | 0
2017-01-01 | GB      | 2500         | TAX3     | 20          | 1000           | 200 

这背后的想法是发票与税收有has_many关系。所以发票可以有税，也可以没有税。报告应显示给定国家/地区收取的总金额 (total_amount)(无论是否包含税费)并指出总金额中的哪一部分应针对特定税收征税 (taxable_amount)。

我目前的方法是这样的:

SELECT 
DATE_FORMAT(invoices.issued_at, '%Y-%m-01') AS rep_month,
invoices.country AS country
( SELECT sum(docs.gross_amount)
  FROM invoices AS docs
  WHERE docs.country = invoices.country
  AND DATE_FORMAT(docs.issue_date, '%Y-%m-01') = rep_month
) AS total_amount,
taxes.tax_name AS tax_name,
taxes.tax_rate AS tax_rate,
SUM(taxes.taxable_amount) AS taxable_amount,
SUM(taxes.tax_amount) AS tax_amount
FROM invoices
JOIN taxes ON invoices.id = taxes.document_id
AND documents.issue_date BETWEEN '2016-01-01' AND '2017-12-31'
GROUP BY account_id, rep_month, country, tax_name, tax_rate
ORDER BY country desc

嗯，这可行，但对于真实的数据集(数千条记录)来说，它真的很慢，因为用于检索 total_amount 的每一行都运行 select 子查询。报告。我无法使用直接 SUM(gross_amount) 作为按税名和税率分组的 GROUP BY 来创建 LEFT JOIN 税，我需要显示每个国家收取的总额，无论金额是否征税。有没有更快的替代方案？

Answer 1

我不知道使用此查询的确切用例，但问题是您尝试构建数据库的方式，您试图一次性获取整个数据。

理想情况下，您应该运行现有的查询并将其存储在不同的表(汇总表)中，然后在需要时直接从汇总表中查询。如果发票表中有一个新条目，那么您可以使用查询在每个条目上运行或通过 cronjob 定期更新汇总表。