mysql - 包括缺失日期按日期分组和运行总计

Question

我有一个如下表:

transID | date | payee | amount
1       | 7/1/2016 | Balance| 100.00
2       | 7/1/2016 | Bobi | -11.11
3       | 7/4/2016 | Chris | -20.76
4       | 7/7/2016 | Erin  | -100.00
5       | 7/11/2016| Tom   | -2.11
6       | 7/11/2016| Pay   | 500.00

我在解决如何按天分组、求和以及包含缺失日期时遇到问题。我试图让选择看起来像这样:

date  | balance
7/1/2016 | 88.89
7/2/2016 | 88.89
7/3/2016 | 88.89
7/4/2016 | 68.13
7/5/2016 | 68.13
7/6/2016 | 68.13
7/7/2016 | -31.87
7/8/2016 | -31.87
7/9/2016 | -31.87
7/10/2016 | -31.87
7/11/2016 | 466.02

基本上，我试图获取每个分组日期之间的所有日期并保留余额。这就像银行账户日常运作一样。

Answer 1

只是需要这样的东西，也许这不是正确的方法，但对我有用:)

SELECT 
    *
FROM
    (SELECT 
        '2016-07-01' + INTERVAL a + b DAY myDate
    FROM
        (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d, (SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40) m
    WHERE
        '2016-07-01' + INTERVAL a + b DAY < '2016-08-01'
    ORDER BY a + b) t1
        LEFT JOIN
    (SELECT 
        COUNT(*) AS myCount, DATE(your_date_field) AS myFS
    FROM
        your_table_that_misses_dates
    GROUP BY myFS) t2 ON t1.myDate = t2.myFS order by myDate;

它会给你这样的东西: