我有多个表格，这些表格从费用计算阶段到开发票阶段都跟在偿还请求之后，记录了请求的每个阶段的行项目及其值。
为了获得每个阶段每个费用类别的总计，我使用选择对它们进行了汇总-我无法弄清楚的是一种有效的方法，可以将整个阶段的总计总计，而不必执行其他总计请求。由于这查询了成千上万的行，因此响应时间受到了影响。当前方法耗时不到4.59秒-而当我删除总计时需要4.02秒-关于如何对原始和进行别名以获得总计的任何建议将是最受欢迎的：
SELECT p.program_name, p.id as program_id, p.entity_id, a.id as account_id,
(SELECT COALESCE(SUM(amount),0) FROM cost_items c WHERE expense_category_id = 9 and c.account_id = a.id) as NIPLoanCost,
(SELECT COALESCE(SUM(amount),0) FROM cost_items c WHERE expense_category_id = 2 and c.account_id = a.id) as AcquisitionCost,
(SELECT COALESCE(SUM(amount),0) FROM cost_items c WHERE expense_category_id = 3 and c.account_id = a.id) as PreDemoCost,
(SELECT COALESCE(SUM(amount),0) FROM cost_items c WHERE expense_category_id = 4 and c.account_id = a.id) as DemolitionCost,
(SELECT COALESCE(SUM(amount),0) FROM cost_items c WHERE expense_category_id = 5 and c.account_id = a.id) as GreeningCost,
(SELECT COALESCE(SUM(amount),0) FROM cost_items c WHERE expense_category_id = 6 and c.account_id = a.id) as MaintenanceCost,
(SELECT COALESCE(SUM(amount),0) FROM cost_items c WHERE expense_category_id = 7 and c.account_id = a.id) as AdministrationCost,
(SELECT COALESCE(SUM(amount),0) FROM cost_items c WHERE expense_category_id = 8 and c.account_id = a.id) as OtherCost,
(SELECT COALESCE(SUM(amount),0) FROM cost_items c WHERE c.account_id = a.id)
as TotalCost,
/* I have removed the other 4 table's requests that follow this same methodology */
FROM programs p, accounts a WHERE p.entity_id = a.entity_id;
您可以将条件聚合与cost_items表的联接一起使用，以替换所有子查询。这样的事情应该起作用：
SELECT p.program_name,
p.id as program_id,
p.entity_id,
t.*
FROM programs p
INNER JOIN accounts a
ON p.entity_id = a.entity_id
INNER JOIN
(
SELECT a.id AS account_id,
SUM(CASE WHEN c.expense_category_id = 9 THEN c.amount ELSE 0 END) AS NIPLoanCost,
SUM(CASE WHEN c.expense_category_id = 2 THEN c.amount ELSE 0 END) AS AcquisitionCost,
SUM(CASE WHEN c.expense_category_id = 3 THEN c.amount ELSE 0 END) AS PreDemoCost,
SUM(CASE WHEN c.expense_category_id = 4 THEN c.amount ELSE 0 END) AS DemolitionCost,
SUM(CASE WHEN c.expense_category_id = 5 THEN c.amount ELSE 0 END) AS GreeningCost,
SUM(CASE WHEN c.expense_category_id = 6 THEN c.amount ELSE 0 END) AS MaintenanceCost,
SUM(CASE WHEN c.expense_category_id = 7 THEN c.amount ELSE 0 END) AS AdministrationCost,
SUM(CASE WHEN c.expense_category_id = 8 THEN c.amount ELSE 0 END) AS OtherCost,
COALESECE(SUM(c.amount), 0) AS TotalCost
FROM accounts a
LEFT JOIN cost_items c
ON a.id = c.account_id
GROUP BY a.id
) t
ON a.id = t.account_id
请注意，您原始方法的缺陷是与以下子查询相关的子查询：
(SELECT COALESCE(SUM(amount),0) FROM cost_items c WHERE expense_category_id = 9 and c.account_id = a.id)
对于programsandaccounts表的联接产品中的每个记录，您都在发出一个单独的查询（实际上是其中的许多查询），该查询正在扫描整个cost_items表以确定总和。在我的方法，我们做一个单次通过的加入accounts和cost_items，这是在节省时间。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句