来自两个单独表的 SQL 结果（差异）

Question

我有两个表 -

INCOME

Id - ProjectId - inAmount 
1  - 2              200   
2  - 2              100
3  - 1              100
4  - 2              100
5  - 1              200

Expense

Id - ProjectId - exAmount
1  - 2           50        
2  - 1           100
3  - 2           120
4  - 1           70

现在我想要这些结果

Result

ProjectId - Total Income - Total Expense - Difference 
1           result         result          result
2           result         result          result

请记住：我必须在 SQL 中完成的所有这些工作

我已经完成了总计收入、总支出，但是更好的差异方法是什么？

----- 已编辑......

我已经尝试过

SELECT sum(ex_amount) as expense, 
       sum(in_amount) as income,  
       sum(in_amount) - sum(ex_amount) as Difference,  
       project_name
FROM expense, 
     project, 
     income
WHERE expense.projectId = Project.id 
  AND income.ProejctId = Project.id
group by mh_name";

第二次编辑

好吧，请理解原来的逻辑。表费用有很多与项目相关的记录，表收入也有相同的现在我想要每个项目的总收入、每个项目的总费用和每个项目的差异的结果

写下你的答案使用这些字段

收入

in_id  - in_source -  in_amount 
    1  - 2              200   
    2  - 2              100
    3  - 1              100
    4  - 2              100
    5  - 1              200

费用

ex_id - mh_id -     ex_amount
    1  - 2           50        
    2  - 1           100
    3  - 2           120
    4  - 1           70

ma​​in_head 或项目

mh_id -  mh_name
    1  -  abc
    2  -  ase
    3  -  czz
    4  -  xys

注释 in_source = mh_id

此时，我正在使用以下查询

来获取每个项目的总收入

SELECT sum(in_amount) as amount, mh_name FROM income, main_head WHERE income.in_source = main_head.mh_id group by mh_name Order By amount desc

每个项目的总费用

SELECT sum(ex_amount) as amount, mh_name FROM expense, main_head WHERE expense.mh_id = main_head.mh_id group by mh_name Order By amount desc

由我解决......

SELECT
mh_name,
 income - expense AS difference 
FROM 
(SELECT sum(in_amount) AS income, in_source FROM income GROPU BY in_source) AS t1, 
(SELECT sum(ex_amount) AS expense, mh_id FROM expense GROUP BY mh_id) AS t2, 
(SELECT * FROM main_head) AS t3 
WHERE 
t1.in_source = t2.mh_id 
AND
t1.in_source=t3.mh_id

Answer 1

这个 SQL 应该适合你：

我假设收入和支出表之间存在 1 对 1 的关系。否则你可能想使用一些左连接。

SELECT i.ProjectId, 
       sum(inAmount) AS "Total Income",
       sum(exAmount) AS "Total Expense",
       (sum(inAmount) - sum(exAmount)) AS "Difference"
FROM   Income i
JOIN   Expense e
ON     i.project_id = e.project_id
GROUP BY i.ProjectId

更新的答案反映了 0 - 许多收入和 0 - 许多可能的支出：

SELECT i.ProjectId, 
       i.income AS "Total Income",
       e.expense AS "Total Expense",
       (i.income - e.expense) AS "Difference"
FROM   (SELECT ProjectId, sum(inAmount) AS income FROM Income GROUP BY ProjectId) i
FULL OUTER JOIN   (SELECT ProjectId, sum(exAmount) AS expense FROM Income GROUP BY ProjectId) e
ON     i.project_id = e.project_id

Answer 2

select 
    i.ProjectId, 
    sum(i.inamount) as TotalIncome, 
    sum(e.examount) as TotalExpense, 
    sum(i.inamount) - sum(e.examount) as Difference
from income i
inner join expense e
on i.projectid = e.projectid
group by i.projectid

编辑：我们都错了。因为不带子查询的连接将连接所有匹配项，所以它会给我们重复的结果。这是正确的答案和正确的结果，否则您将得到双重值：

select 
    i.projectid,
    i.inamount as Income,
    e.examount as Expense,
    i.inamount - e.examount as [Difference]
from
(
    select projectid, SUM(inamount) as inamount
    from income
    group by projectid
) as i
full outer join
(
    select projectid, SUM(examount) as examount
    from expense
    group by projectid
) as e
on i.projectid = e.projectid

我保留了原来的答案，以便可以比较它们。第二个查询是正确的。

Answer 3

您的尝试和几个答案面临的问题是您正在将数据集连接在一起。

例如，项目 2 有 3 条收入记录和 2 条支出记录。您实际上并不想将它们连接在一起，结果您将得到 6 条记录...

Id - ProjectId - inAmount        Id - ProjectId - exAmount
1  - 2              200           1   2           50
2  - 2              100           1   2           50
4  - 2              100           1   2           50
1  - 2              200           3   2           120
2  - 2              100           3   2           120
4  - 2              100           3   2           120

如您所见，这将导致严重的重复。

有一些标准方法。例如相关子查询或内联视图。

相关子查询...

SELECT
  *,
  (SELECT SUM(inAmount) FROM income  WHERE projectId = project.Id) AS income,
  (SELECT SUM(exAmount) FROM expense WHERE projectId = project.Id) AS expense
FROM
  project

内联视图

SELECT
  project.Id,
  income.inAmount,
  expense.exAmount
FROM
  project
LEFT JOIN
  (SELECT projectID, SUM(inAmount) AS inAmount FROM income  GROUP BY projectID) AS income
    ON income.projectID = project.ID
LEFT JOIN
  (SELECT projectID, SUM(exAmount) AS exAmount FROM expense GROUP BY projectID) AS expense
    ON expense.projectID = project.ID

编辑

对 Hamidam 尝试使用 UNION 的更正...

SELECT
  projectID,
  SUM(inAmount),
  SUM(exAmount)
FROM
(
  SELECT projectID,      inAmount, 0 AS exAmount FROM income  GROUP BY projectID
  UNION ALL
  SELECT projectID, 0 as inAmount,      exAmount FROM expense GROUP BY projectID
)
  AS data
GROUP BY
  projectID

Answer 4

SELECT ge.id AS ProjectID 
     , TotalIncome
     , TotalExpense
     , TotalIncome - TotalExpense AS Difference
FROM 
    ( SELECT p.id
           , COALESCE(SUM(i.in_amount), 0) AS TotalIncome
      FROM project p
        LEFT JOIN income i
          ON i.projectid = p.id
      GROUP BY p.id
    ) ge
  JOIN
    ( SELECT p.id
           , COALESCE(SUM(e.ex_amount), 0) AS TotalExpense
      FROM project p
        LEFT JOIN expense e
          ON e.projectid = p.id
      GROUP BY p.id
    ) gi
    ON ge.id = gi.id