mysql - 收入和费用计算

Question

我需要显示每天的收入和支出

收支不同表

我需要以以下格式显示，例如：

17/08/2019 日期表中有两个收入

我需要计算当日收入的总和，以当天的费用显示在结果上。

我尝试了一些查询，但它不起作用。

Date |  Income | Expense | Profit 

Select SUM(d.amount)
     , SUM(e.amount)
     , d.date
     , e.date 
  FROM due d 
  JOIN expenses e
    ON d.date = e.date

费用表 -table-name : 费用

id | date      |  details  |  amount
1   13-08-2019     daily      50
2   17-08-2019     cleaning   50
3   17-08-2019     cleaning   50
4   18-08-2019     Tea        150 
5   18-08-2019     other      50 

收入表 -table-name : 到期

id | date         |  amount
4   12-08-2019        150
5   13-08-2019        100 
6   18-08-2019        450
7   18-08-2019        50 

结果将是：

id | date       | Income | Expense | Profit
1    12-08-2019   150       NULL      150
2    13-08-2019   100       50        50
3    17-08-2019   NULL      100       -100
4    18-08-2019   500       200       300

标签： mysql

Answer 1

将来，我建议通过使用发布一些表格详细信息，SHOW CREATE TABLE table_name这将使我们能够更好地为您提供帮助。

您应该能够使用联合和一些分组来获得您所追求的：

SELECT
    Date,
    SUM(Income) as Income,
    SUM(Expense) as Expense,
    SUM(Income) - SUM(Expense) as Profit
FROM (
    SELECT
        due.date as Date,
        due.amount as Income,
        0 as Expense
    FROM due
    UNION ALL
    SELECT
        expenses.date as Date
        0 as Income,
        expenses.amount as Expense
    FROM expenses
)
GROUP BY Date