如何从另一个表中从两个不同的表中求和

qnakjoqk 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(329)

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

TBL分类

t笔划

tblincomes公司

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

我尝试使用以下查询，但它返回了错误的数据：

SELECT category.cat_title as Category,date_format(projects.proj_sdate, '%Y') 
as Year, (select sum(incomes.inc_amount) from incomes where 
incomes.projects_id = projects.proj_id) as Total_Income, 
(select sum(expenses.exp_amount) from expenses where expenses.projects_id = 
projects.proj_id) as Total_Expenses,
(select ifnull(sum(incomes.inc_amount),0) from incomes where 
incomes.projects_id = projects.proj_id) - (select 
ifnull(sum(expenses.exp_amount),0) from expenses where expenses.projects_id 
= projects.proj_id) as PnL from category inner join projects on 
projects.proj_cat = category.cat_id group by category.cat_id

mysql

来源：https://stackoverflow.com/questions/53666062/how-to-get-sum-from-2-different-tables-from-another-table

1条答案

按热度按时间

odopli941#

你可以在下面试试-

SELECT category.cat_title as Category,Proj_title,
date_format(projects.proj_sdate, '%Y') as Year,
sum(expenses.exp_amount)  as total_income
coalesce(sum(incomes.inc_amount),0) - sum(expenses.exp_amount) as pnl
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,Proj_title,date_format(projects.proj_sdate, '%Y')

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

我来回答

如何从另一个表中从两个不同的表中求和

1条答案

相关问题

热门标签

最新问答