我有一个表事务,我试图找出给定月份的新客户。这意味着,如果客户在当月之前没有交易,他/她将被视为新客户。
我已经找到了一种方法,但效率很低,而且需要很长时间。然后我发现了这个artikel,它比较了不同的方法。我曾试图将这种方法调整为我的方法,但没有成功。
将我的问题可视化:
|--------------------------- time period with all transactions -----------------------|
|----- period before month transactions = 0) ---|---- curr month transactions > 0 ----|
表格如下:
transactions
id, email, state, date_paid
我的查询:
SELECT
l.email
FROM
transactions as l
LEFT JOIN transactions as r ON r.email = l.email
WHERE
r.email IS NULL
AND l.state = 'paid'
AND r.state = 'paid'
AND l.date_paid <= '2013-12-31 23:59:59'
AND r.date_paid < '2013-12-01 00:00:00'
我做错了什么?
试试这个:
SELECT l.email
FROM transactions AS l
LEFT JOIN transactions AS r ON r.email = l.email AND r.state = 'paid' AND r.date_paid < '2013-12-01 00:00:00'
WHERE r.email IS NULL AND l.state = 'paid' AND l.date_paid <= '2013-12-31 23:59:59'
试试这个:
SELECT l.email
FROM transactions l
WHERE NOT l.email IN (SELECT r.email
FROM transactions r
WHERE r.state = 'paid' AND r.date_paid < '2013-12-01 00:00:00')
AND l.state = 'paid' AND l.date_paid <= '2013-12-31 23:59:59'