我有以下数据,我已经编写了一个查询,以获取在给定日期之前登录但在给定日期之后未登录的所有用户:
+----+------------------+----------------------+
| id | user_login | date |
+----+------------------+----------------------+
| 1 | longtwin | 2018-03-29 22:15:56 |
| 2 | admin | 2018-03-29 22:16:05 |
| 3 | steve | 2018-06-29 22:19:45 |
| 4 | robinbiundo | 2017-03-29 22:56:13 |
| 5 | shannon | 2017-06-29 23:07:38 |
| 6 | long | 2017-04-29 23:40:58 |
| 7 | longtwin | 2017-04-29 23:41:57 |
| 8 | long | 2017-03-30 02:24:46 |
| 9 | long | 2017-03-30 03:11:48 |
| 10 | abigailrashbaum | 2016-03-30 11:50:43 |
| 11 | timothybrown | 2016-03-30 16:25:59 |
| 12 | timothybrown | 2016-03-30 17:09:27 |
| 13 | timothybrown | 2015-03-30 17:28:44 |
| 14 | timothybrown | 2015-03-30 17:33:06 |
| 15 | steve | 2014-03-30 18:03:46 |
| 16 | steve | 2014-03-30 18:04:48 |
| 17 | steve | 201-03-30 19:16:59 |
+----+------------------+----------------------+
我写了下面的SQL,但结果不是预期的,请纠正我的SQL有什么问题?
SELECT p.user_login, COUNT(p.user_login)
FROM wp_login_log_backup p
WHERE p.user_login NOT IN (
SELECT u.user_login
FROM wp_login_log_backup u
WHERE u.date < '2017-04-07 00:00:00'
GROUP BY u.user_login
)
GROUP BY p.user_login;
我只需要
timothybrown, robinbiundo
因为该用户在
2017-04-07 00:00:00