时间戳

首页 > 解决方案 > Mysql SUM And Group 来自多个表，但仅包含某些数据

mysql - Mysql SUM And Group 来自多个表，但仅包含某些数据

问题描述

我有3个数据库表如下：

项目：

id

project_name

project_location

status

项目费用

id

project_id

expense_category

expense_subcategory

amount

userid

date

payment_status

project_status

项目收入

id

project_id

project_income

date

userid

project_status

projects.id、project_expenses.project_id 和 project_income.project_id 是相关的。

我需要创建一个查询，显示Project_ID、Project_Name、Project_Income 的 SUM、Project_expenses 的 SUM、Difference

我尝试了以下查询。

SELECT p.id AS id, p.project_name AS project_name
,(SELECT SUM(i.project_income)
FROM project_income i
WHERE i.project_id = p.id 
)AS income,
(SELECT SUM(e.amount)
FROM project_expenses e
WHERE e.project_id = p.id 
)AS expenses,
(SELECT (income - expenses)) AS difference
FROM projects p
WHERE p.status = 'Active'

但也列出了一些“没有任何收入和支出的项目”。我只需要显示那些有费用和收入的项目。

这怎么可能。请求帮助...

标签： mysql

解决方案

SELECT projects.id AS id, 
       projects.project_name AS project_name, 
       SUM(totals.income) income, 
       SUM(totals.expenses) expenses,
       SUM(totals.income) - SUM(totals.expenses) difference
FROM ( SELECT SUM(project_income) income,
              0 expenses,
              project_id
       FROM project_income i
       GROUP BY project_id
     UNION ALL
       SELECT 0,
              SUM(e.amount),
              project_id
       FROM project_expenses e
       GROUP BY project_id ) totals
JOIN projects ON totals.project_id = projects.id
WHERE projects.status = 'Active'
GROUP BY projects.id, 
         projects.project_name

仅列出具有至少一项收入和/或支出的项目。

推荐阅读