用于显示按日期分组的数据(按天递增)的 MySQL 查询

Question

我一直在尝试想出一个查询来以特定方式显示数据，但我一直在努力解决这个问题。

假设这些数据

ID Amount Date
1  10     10/10/2014 13:45
1  20     10/10/2014 14:56
1  05     10/10/2014 22:45
1  10     11/10/2014 23:04
1  30     14/10/2014 03:00
1  15     14/10/2014 04:34
1  15     15/10/2014 13:34
2  10     10/10/2014 16:05

我想调用具有开始日期和结束日期的查询，并使用包含该 ID 当天金额总和的每日行显示结果。所以结果是:

对于 Id=1、StartDate=10/10/2014 和 EndDate=14/10/2014:

Date        Sum
10/10/2014  35
11/10/2014  10
12/10/2014   0
13/10/2014   0
14/10/2014  45

现在回到我已经尝试过的事情，我在 jFiddle 中创建了一个脚本:

CREATE TABLE TableName
    (`id` int, `amount` int, `timestamp` datetime)
;

INSERT INTO TableName
    (`id`, `amount`, `timestamp`)
VALUES
    (1, 10, '2013-01-06 12:23:56'),
    (1, 15, '2013-01-06 02:23:41'),
    (1, 15, '2013-01-07 14:23:42'),
    (1, 0,  '2013-01-08 04:23:56'),
    (1, 5,  '2013-01-08 16:23:25'),
    (1, 20, '2013-01-08 12:23:57'),
    (1, 10, '2013-01-08 23:23:40'),
    (1, 0,  '2013-01-09 07:23:56'),
    (1, 5,  '2013-01-12 17:23:25'),
    (1, 20, '2013-01-13 22:23:57'),
    (1, 10, '2013-01-14 09:23:40'),
    (1, 10, '2013-01-14 19:23:23'),
    (1, 35, '2013-01-15 15:23:55'),
    (1, 40, '2013-01-15 21:29:38'),
    (2, 40, '2013-01-06 02:31:59');

我用它作为 Playground ，使用了我在互联网上找到的各种方法，但运气不佳。

知道如何获取该结构中的数据吗？

Answer 1

您需要生成所有日期。这很痛苦，但以下方法适用于您的数据:

select d.dte, coalesce(sum(tn.amount), 0)
from ((select date(timestamp) as dte from tablename) union
      (select date(timestamp) + interval 1 day from tablename) union
      (select date(timestamp) - interval 1 day from tablename)
     ) d left outer join
     tablename tn
     on date(tn.timestamp) = d.dte
group by d.dte
order by 1;

编辑:

如果您想将这些限制为表格中的日期，则添加:

where d.dte between (select min(date(timestamp) from tablename) and
                    (select max(date(timestamp) from tablename)

如果您的差距较大，请在 d 子查询中放置更多的 selects，或者使用日历表(后者更容易)。

Here是 SQL fiddle 。