考虑到刚刚发生的那件事,我想要一些记录.在这种情况下,我们有一些用户,我们需要他们之前在2018年创建的订阅.
我们有这个订阅表:
id | user_id | created_at |
---|---|---|
1 | 1 | 2016-01-01 |
2 | 1 | 2017-01-01 |
3 | 1 | 2018-01-01 |
4 | 1 | 2019-01-01 |
5 | 1 | 2020-01-01 |
6 | 2 | 2018-01-01 |
7 | 2 | 2019-01-01 |
我使用的是自连接:
SELECT `subscriptions`.`id`
FROM `subscriptions`
LEFT JOIN subscriptions as previous
ON subscriptions.user_id = previous.user_id AND subscriptions.created_at > previous.created_at
WHERE `previous`.`created_at` BETWEEN '2018-01-01' AND '2018-12-31';
它返回4,5,7
,但我只想要紧随其后的4,7