我有用户谁支付每月支付的数据库。我需要检查是否有这些款项的连续性。 例如下表中:
I have a database of users who pay monthly payment. I need to check if there is continuity in these payments. For example in the table below:
+---------+------------+
| user_id | date |
+---------+------------+
| 1 | 2015-02-01 |
| 2 | 2015-02-01 |
| 3 | 2015-02-01 |
| 1 | 2015-03-01 |
| 2 | 2015-03-01 |
| 3 | 2015-03-01 |
| 4 | 2015-03-01 |
| 1 | 2015-04-01 |
| 2 | 2015-04-01 |
| 3 | 2015-04-01 |
| 4 | 2015-04-01 |
| 5 | 2015-04-01 |
| 1 | 2015-05-01 |
| 2 | 2015-05-01 |
| 3 | 2015-05-01 |
| 4 | 2015-05-01 |
| 5 | 2015-05-01 |
| 1 | 2015-06-01 |
| 2 | 2015-06-01 |
| 3 | 2015-06-01 |
| 5 | 2015-06-01 |
| 3 | 2015-07-01 |
| 4 | 2015-07-01 |
| 5 | 2015-07-01 |
+---------+------------+
直到5月一切正常,但6月4用户没有支付他虽然在接下来的一个月(7月)支付。 七月用户1和2没有支付,但这是正常的,因为他们可以辞去服务。 因此,在这种情况下,我需要有信息的用户4没有支付六月。 是否有可能做到这一点使用SQL?
Until May everything was ok, but in June user 4 didn't pay although he paid in the next month (July). In July users 1 and 2 didn't pay, but this is ok, because they could resign from the service. So in this case I need to have information "User 4 didn't pay in June". Is it possible to do that using SQL?
我使用的MS Access,如果有必要的信息。
I use MS Access if it's necessary information.
我已经写了一个简单的查询,但我知道,这不是最好的解决办法。其他的解决方案仍然是值得欢迎的。
I have written a simple query for this but I realize that it's not the best solution. Other solutions are still welcome.
SELECT user_id,
MIN(date) AS min_date,
MAX(date) AS max_date,
COUNT(*) AS no_of_records,
round((MAX(date)-MIN(date))/30.4+1,0) AS months,
(months-no_of_records) AS diff
FROM test
GROUP BY user_id
HAVING (round((MAX(date)-MIN(date))/30.4+1,0)-COUNT(*)) > 0
ORDER BY 6 DESC;
现在我们可以来看看列no_of_records和月。如果它们不相等,有此用户的间隙。
Now we can take a look at columns "no_of_records" and "months". If they are not equal, there was a gap for this user.