mysql - 寻找客户和他们的第一笔付款

Question

我有 3 张 table 。

帐户:

id
1
2
3 

客户:

id | account | email
76276 | 1 | test1@mail.com
143158 | 2 | test2@mail.com
143159 | 3 | test3@mail.com

付款:

id | customer | date
285041 | 76276 | 2014-12-01 00:13:41
285042 | 76276 | 2014-12-01 00:15:55
285043 | 143158 | 2014-12-01 00:18:52
285044 | 143159 | 2014-12-02 12:21:47

我想获取客户首次付款在2014-12-01 00:00:00到2014-12-01 23:59:59之间的所有账户

我试过了

SELECT a.id
FROM account a
JOIN customer c ON c.account = a.id
JOIN payment p ON p.id = (
    SELECT p.id 
    FROM payment p 
    WHERE p.customer = c.id 
    AND p.date BETWEEN '2014-12-01 00:00:00' AND '2014-12-01 00:59:59'
    ORDER BY date ASC 
    LIMIT 1
)

但是这个查询运行了很长时间......而且我很确定这不是我应该加入付款表的方式。一些帮助将不胜感激。

Answer 1

使用min()查找首次付款日期。然后获取适当的信息:

select account
from customers c join
     payments p
     on p.customer = c.id
group by account
having min(p.date) >= '2014-12-01' and min(p.date)  < '2014-12-02';