mysql - 从另一个表获取2个不同表的SUM

我正在研究费用/预算跟踪系统，并且遇到了某些问题。
我有4张桌子
tblProjects

tbl类别



tblExpenses


tbl收入



  tblProject与1：1的tblCategory相关，
  
  tblExpenses与tblProjects的比例也为1：1，
  
  与1：1的tblProject有关的tblIncomes也是如此


我正在尝试按年份（从tblProjects.proj_sdate列）获取每个类别组的费用和收入的总和，再加上一列将显示损益表（费用-收入）。
例如，我想知道什么是总支出，总收入，为旅行，体育等支出的损益的价值？
下面是我要实现的示例；

我设法提出了以下两个查询，将收入和支出分开。但不确定如何组合使其成为单个查询并执行减法运算以获取P＆L值。

1)Incomes
SELECT category.cat_title as Category, group_concat(distinct
projects.proj_title) as Projects, date_format(projects.proj_sdate, '%Y') as
Year, sum(incomes.inc_amount) as Total_Incomes from category inner join
projects on projects.proj_cat = category.cat_id inner join incomes on
incomes.projects_id = projects.proj_id group by category.cat_title,
date_format(projects.proj_sdate, '%Y')

2)Expenses
SELECT category.cat_title as Category, group_concat(distinct
projects.proj_title) as Projects, date_format(projects.proj_sdate, '%Y') as
Year, sum(expenses.exp_amount) as Total_Expenses from category inner join
projects on projects.proj_cat = category.cat_id inner join expenses on
expenses.projects_id = projects.proj_id group by category.cat_title,
date_format(projects.proj_sdate, '%Y')

最佳答案

我认为该查询将为您提供所需的结果。由于每个项目可以有多个支出和收入行，因此需要JOIN来汇总收入和支出表（按项目）而不是原始表。然后将所有JOIN的结果按Category和Year分组：

SELECT c.cat_title AS Category,
       GROUP_CONCAT(p.proj_title) AS Projects,
       YEAR(p.proj_sdate) AS Year,
       COALESCE(SUM(i.income), 0) AS Total_Income,
       COALESCE(SUM(e.expenses), 0) AS Total_Expenses,
       COALESCE(SUM(i.income), 0) - COALESCE(SUM(e.expenses), 0) AS `P&L`
FROM tblCategory c
JOIN tblProjects p ON p.proj_cat = c.cat_id
LEFT JOIN (SELECT projects_id, SUM(exp_amount) AS expenses
           FROM tblExpenses
           GROUP BY projects_id) e ON e.projects_id = p.proj_id
LEFT JOIN  (SELECT projects_id, SUM(inc_amount) AS income
           FROM tblIncome
           GROUP BY projects_id) i ON i.projects_id = p.proj_id
GROUP BY Category, Year

结果（针对您问题中的示例数据）：

Category    Projects                    Year    Total_Income    Total_Expenses  P&L
KSS Talks   Projects XYZ                2017    2.00            152.00          -150.00
KSS Talks   Projects X,Projects Satu    2018    371.00          365.00          6.00
Sports      Projects Y                  2018    150.00          0.00            150.00
Trip        Projects Z                  2018    15.00           0.00            15.00

Demo on dbfiddle