mysql - 如何使用子查询来统计上个月的记录

Question

我有一个看起来像这样的表:

+-----+------------+-----+--------+------ ----------+--------------------+
|编号 |潮 |进程号 |金额 |穿越时空 |创建日期 |
+-----+------------+-----+---------+------------ ---+--------------------+
| 784 | LDC3N0JF1Z | 784 | 300.0000 | 20170412174304 | 2017-04-12 17:43:42 |
| 783 | LDC6MWNHO8 | 783 | 500.0000 | 20170412124952 | 2017-05-12 12:50:15 |
| 782| LDB1MPK8BP | 782| 200.0000 | 20170411192815 | 2017-05-11 19:28:19 |
| 781| LDA2MAUW08 | 781| 200.0000 | 20170410174635 | 2017-07-10 17:47:01 |
| 780 | LDA8MAMQT4 | 780 | 200.0000 | 20170410173245 | 2017-04-10 17:33:19 |
+-----+------------+-----+---------+------------ ---+--------------------+


我想要一个查询给我这样的结果。我想要的结果如下所示。

id|tid        |this_month_amount|amount_previous_month |amount_difference
------------------------------------------------------------------
1 |LDC3N0JF1Z |300              | 700                  |400

我也试过这个问题。这就是我的。

选择 payment.id,payment.tid,payment.DateCreated as dc,payment.amount as this_month_amount, inner join
(SELECT sum(amount) as t2 sum group by amount from payments where DateCreated= DATEADD(mm,-1,dc))payments.id 上的子查询 = subquery.id order by id desc limit 5
)

因此查询应该选择月份的金额，然后是前几个月的金额，然后在名为 amount_difference 的字段中显示差异

错了，试了很多，还想请大家帮忙。谢谢。

Answer 1

获取当前月份的值:

select tid, sum(amount) as this_month,
from t
where date_created >= curdate() + interval (1 - day(curdate()) day and
      date_created < curdate() + interval (1 - day(curdate()) day + interval 1 month
group by tid;

请注意，这仅在当前日期谨慎使用函数，因此 MySQL 可以在 date_create 上使用索引(如果可用的话)。

要获取上个月的金额，让我们使用条件聚合对其进行扩展:

select tid,
       sum(case when month(date_created) = month(curdate()) then amount end) as this_month,
       sum(case when month(date_created) <> month(curdate()) then amount end) as last_month,
       (sum(case when month(date_created) = month(curdate()) then amount end) -
        sum(case when month(date_created) <> month(curdate()) then amount end)
       ) as diff
from t
where date_created >= curdate() + interval (1 - day(curdate()) day -

间隔 1 个月和 date_created < curdate() + interval (1 - day(curdate()) day + interval 1 month 按时间分组；