在求和和分组时连接多个表的SQL

Question

我正在寻找如何在多个表上构建MS Access SQL查询的帮助，并总结按项目分组的结果和不同类型金融交易的美元金额的总和。

Project表包含关于项目的元数据。实际、预算和预测表是对项目的实际和/或计划的财务交易的记录。

表和字段(为本文简化)如下:

Project
--------------
project_id
project_name
project_manager
Budget
--------------
entry_date
project_id
amount
Actuals
--------------
entry_date
project_id
amount
Forecast
--------------
entry_date
project_id
amount

SQL尝试:

SELECT
p.[project_id],
SUM(b.[amount]) AS budget_amount,
SUM(a.[amount]) AS actual_amount,
SUM(f.[amount]) AS forecast_amount
FROM
Project AS p,
Budget AS b,
Actuals AS a,
Forecast AS f
WHERE
p.[project_id] = b.[project_id]
AND b.[project_id] = a.[project_id]
AND a.[project_id] = f.[project_id]
GROUP BY
p.[project_id];

预期的结果:

project_id  budget_amount   actual_amount   forecast_amount
2021-001    $2,000.00   $2,900.00   $2,400.00
2021-002    $3,000.00   $3,500.00   $5,000.00

实际结果:

project_id  budget_amount   actual_amount   forecast_amount
2021-001    $12,000.00  $17,400.00  $9,600.00
2021-002    $18,000.00  $21,000.00  $20,000.00

样本数据:

项目

project_id  project_name    project_manager
2021-001    First Project of 2021   Jack Black
2021-002    Second Project of 2021  James Dean

实绩

entry_date  project_id  amount
1/9/2021    2021-001    $1,300.00
1/23/2021   2021-001    $1,600.00
1/9/2021    2021-002    $1,500.00
1/23/2021   2021-002    $2,000.00

预算

entry_date  project_id  amount
1/9/2021    2021-001    $1,000.00
1/23/2021   2021-001    $1,000.00
1/9/2021    2021-002    $1,500.00
1/23/2021   2021-002    $1,500.00

预测

entry_date  project_id  amount
1/9/2021    2021-001    $900.00
1/23/2021   2021-001    $1,000.00
2/6/2021    2021-001    $500.00
1/9/2021    2021-002    $1,500.00
1/23/2021   2021-002    $3,000.00
2/6/2021    2021-002    $500.00

Answer 1

您需要做的是在您将它们连接到最终查询之前，在项目粒度上独立创建每个摘要。

这个更改将所有子查询放在一个查询中运行。假设project_id是项目表中的唯一键，这意味着:

SELECT
p.[project_id],
budget_amount,
actual_amount,
forecast_amount
FROM
Project AS p left join 
(SELECT
[project_id],
SUM([amount]) AS budget_amount,
FROM
Budget
GROUP BY
[project_id]) AS b on p.project_id=b.project_id
Left join 
(SELECT
[project_id],
SUM([amount]) AS actual_amount,
FROM
Actuals
GROUP BY
[project_id]) AS a on p.project_id = a.project_id
Left join 
(SELECT
[project_id],
SUM([amount]) AS forecast_amount,
FROM
forecast
GROUP BY
[project_id]) AS f on p.project_id = f.project_id

左边的项目表连接所有其他摘要，因为假定它包含一个项目列表，这些项目可能存在于/可能不存在于其他摘要中。

Answer 2

我真的希望有一个更优雅的方法。而且，我很有信心有更好的方法，但这有效，希望我能保住饭碗。

当然欢迎反馈。

SELECT
project_id,
budget_amount,
actual_amount,
forecast_amount
FROM (
SELECT b.[project_id] as fs_project_id, budget_amount, actual_amount
FROM
(   
SELECT
b.[project_id],
SUM(b.[amount]) AS budget_amount
FROM
Budget AS b
GROUP BY
b.[project_id]
) AS q_BudgetSum
LEFT JOIN (
SELECT
a.[project_id],
SUM(a.[amount]) AS actual_amount
FROM
Actuals AS a
GROUP BY
a.[project_id]
) AS q_ActualSum ON q_ActualSum.[project_id] = q_BudgetSum.[project_id]
) AS FirstSet
LEFT JOIN
( SELECT p.[project_id] as project_id, forecast_amount
FROM
(   
SELECT *
FROM
Project AS p
) AS q_Project
LEFT JOIN (
SELECT
f.[project_id],
SUM(f.[amount]) AS forecast_amount
FROM
Forecast AS f
GROUP BY
f.[project_id]
) AS q_ForecastSum ON q_ForecastSum.[project_id] = q_Project.[project_id]
)
AS SecondSet
ON FirstSet.[fs_project_id] = SecondSet.[project_id];