mysql - 计算特定日期之前发生的次数

Question

在 MySQL 中，我有一个包含这些列的记录表:id,date,voucher_id,person_id

示例数据库

id         Date         voucher_id   person_id
-----      ------       ----------   ----------
0          2016-02-17   null         1
1          2016-02-18   null         1
2          2016-02-19   1            1
3          2016-02-20   2            2
4          2016-02-21   null         2
5          2016-02-22   3            2
6          2016-02-23   null         1
7          2016-02-24   4            1
8          2016-02-24   null         3
9          2016-02-25   null         3

我想知道使用代金券的人的名单，并计算在该代金券使用日之前该人在我的表中记录了多少次。

about 表的结果是:

 Date         voucher_id   person_id    count_till_now
 ------       -------      ----------   --------------
 2016-02-19   1            1            2
 2016-02-20   2            2            0
 2016-02-22   3            2            1
 2016-02-24   4            1            4

生成此结果的最佳查询是什么？

Answer 1

一种方法是使用相关子查询:

SELECT `Date`, voucher_id, person_id,
       (SELECT COUNT(*) 
        FROM mytable AS t2
        WHERE t2.person_id = t1.person_id AND
              t2.`Date` < t1.`Date`) AS count_till_now
FROM mytable AS t1
WHERE t1.voucher_id IS NOT NULL

Demo here

或者你可以使用LEFT JOIN:

SELECT t1.`Date`, t1.voucher_id, t1.person_id, 
       COUNT(t2.person_id) AS count_till_now
FROM mytable AS t1
LEFT JOIN mytable AS t2 ON t1.person_id = t2.person_id AND t1.`Date` > t2.`Date`
WHERE t1.voucher_id IS NOT NULL
GROUP BY t1.`Date`, t1.voucher_id, t1.person_id

Demo here