mysql - SQL查询: calculate profile or loss in given 2 tables

Question

我有 2 个表存储收入:

id    name     date         price     isDel
1     abc     2012-01-26    613.90       0
2     cde     2012-01-25    530.10       0

及费用:

id     name   price    date         isDel
1      b1     334.90   2012-01-26   0
2      b1     41.90    2012-01-25   0
3      d1     61.90    2012-01-25   0

我想得到如下结果:

date        total income         total expense             profile n loss
2012-01-25  530.10                103.80                    426.30
2012-01-26  613.90                344.90                    26.90

谢谢。谁能提供SQL的解决方案吗？我确实在 stackoverflow 上引用了一些成员的问题和答案，但我无法修改为我需要的。请帮忙。

Answer 1

如果您希望每天盈利/亏损，这应该可以:

SELECT Inc.date, TotalInc, TotalExp, TotalInc - TotalExp AS ProfitNLoss
FROM
(
  SELECT date, SUM(price) as TotalInc
  FROM Income
  WHERE isdel = 0
  GROUP BY date) as Inc
LEFT OUTER JOIN 
(

  SELECT date, SUM(price) as TotalExp
  FROM Expense
  WHERE isdel = 0
  GROUP BY date) as Exp

ON Inc.date = Exp.date

UNION

SELECT Exp.date, TotalInc, TotalExp, TotalInc - TotalExp AS ProfitNLoss
FROM
(
  SELECT date, SUM(price) as TotalInc
  FROM Income
  WHERE isdel = 0
  GROUP BY date) as Inc
RIGHT OUTER JOIN 
(

  SELECT date, SUM(price) as TotalExp
  FROM Expense
  WHERE isdel = 0
  GROUP BY date) as Exp

ON Inc.date = Exp.date