从另一个表中获取两个不同表的和

rryofs0p 于 2021-06-15 发布在 Mysql

关注(0)|答案(2)|浏览(215)

我在做一个费用/预算跟踪系统，在某个地方卡住了。
我有四张table
TBL项目

TBL分类

t笔划

tblincomes公司

TBL项目与TBL类别的比例为1:1，
TBL费用与TBL项目的比例也为1:1，
同样适用于TBL项目，与TBL项目的比例为1:1
我试图得到每年每个类别组的费用和收入的总和（从列中） tblProjects.proj_sdate )，加上一列将显示损益（支出-收入）。
例如，我想知道花费的总额，收入的总额，旅行、体育等的损益值。
下面是我想要达到的目标的例子；

我设法提出了两个查询，下面分别是收入和支出。但不知道如何组合它，使它成为一个单一的查询和执行减法以及获得损益值。

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')

mysql

来源：https://stackoverflow.com/questions/53810144/getting-sum-of-2-different-table-from-another-table

2条答案

按热度按时间

8ehkhllq1#

你可以在下面试试

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,
sum(expenses.exp_amount) as Total_Expenses,
sum(incomes.inc_amount) -  sum(expenses.exp_amount) as PL
from category inner join projects on projects.proj_cat = category.cat_id 
inner join expenses on expenses.projects_id = projects.proj_id
inner join incomes on incomes.projects_id = projects.proj_id
group by category.cat_title, 
date_format(projects.proj_sdate, '%Y')

赞(0）回复(0）举报 2021-06-16

lfapxunr2#

我想这个查询会给你你想要的结果。因为每个项目可以有多个支出和收入行，所以需要连接到收入和支出汇总表（按项目），而不是原始表。所有测试的结果 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

在dbfiddle上演示

赞(0）回复(0）举报 2021-06-16

我来回答

从另一个表中获取两个不同表的和

2条答案

相关问题

热门标签

最新问答