英文:
How can I get members with that dont have active subscriptions?
问题
我有两个表，members 和 subscriptions。
### members 表 ###
| id|
| -------- |
| 1 |
| 2 |
| 3 |
|4 |
|5 |
### subscriptions 表 ###
| id | member id | start_date| end_date |
| -------- | -------------- | -------- | -------------- |
| 1| 3| 2023-08-05| 2023-09-05|
| 1| 3| 2023-07-20| 2023-10-11|
| 1| 2| 2023-09-01| 2023-10-01|
| 1| 2| 2023-08-09| 2023-11-02|
| 1| 2| 2023-06-01| 2023-07-01|
| 1| 4| 2023-05-03| 2023-06-21|
| 1| 4| 2023-06-21| 2023-07-25|
| 1| 5| 2023-08-10| 2023-09-26|
| 1| 5| 2023-09-04| 2023-11-04|
我想在一个联接表中获取那些没有任何订阅的成员，这些订阅的开始日期小于今天，结束日期大于今天，这意味着在我的情况下是活跃的订阅。我还想要获取已获得表的最新结束订阅的开始日期和结束日期，例如；
假设今天是 2023-07-31：
### 所需结果表 ###
| id (成员 id) | start_date(最新订阅开始日期)| end_date(最新订阅结束日期) |
| -------- | -------- | -------------- |
| 2| 2023-06-01| 2023-07-01|
| 3| 2023-06-21| 2023-07-25|
| 5| 2023-08-10| 2023-09-26|
如何用 SQL 实现？先感谢您。
英文:
I have two tables members and subscriptions.
members table
| id |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
subscriptions table
| id | member id | start_date | end_date |
|---|---|---|---|
| 1 | 3 | 2023-08-05 | 2023-09-05 |
| 1 | 3 | 2023-07-20 | 2023-10-11 |
| 1 | 2 | 2023-09-01 | 2023-10-01 |
| 1 | 2 | 2023-08-09 | 2023-11-02 |
| 1 | 2 | 2023-06-01 | 2023-07-01 |
| 1 | 4 | 2023-05-03 | 2023-06-21 |
| 1 | 4 | 2023-06-21 | 2023-07-25 |
| 1 | 5 | 2023-08-10 | 2023-09-26 |
| 1 | 5 | 2023-09-04 | 2023-11-04 |
I want to get members in a joined table where they don't have any subscription that has start_date < today and end date > today. Which means active subscription in my case. I also want to get latest ended subscription's start_date and end_date for acquired table such as;
let's assume today is 2023-07-31:
required result table
| id (member_id) | start_date(latest_sub start_date) | end_date(latest_sub end_date) |
|---|---|---|
| 2 | 2023-06-01 | 2023-07-01 |
| 3 | 2023-06-21 | 2023-07-25 |
| 5 | 2023-08-10 | 2023-09-26 |
How can I achive it with sql? thanks in advance.
答案1
得分: 2
你可以使用以下代码：
SELECT member_id FROM subscriptions WHERE CAST(GETDATE() AS Date) NOT BETWEEN start_date AND end_date;
这将按照你的要求选择member_id。关于GETDATE()，你可以在这个教程上了解更多信息。
如果GETDATE()不起作用，请考虑在帖子中标记你的DMS。
编辑：你可以通过以下方式获取成员信息：
SELECT * FROM members WHERE id = (SELECT member_id FROM subscriptions WHERE CAST(GETDATE() AS Date) NOT BETWEEN start_date AND end_date);
这将查找与上一个请求中的结果中的id相同的成员。
英文:
You can use :
SELECT member_id FROM subscriptions WHERE CAST( GETDATE() AS Date ) NOT BETWEEN start_date AND end_date;
wich will select the member_id as you ask.
You can learn more about GETDATE() on this tutorial
If GETDATE() doesn't work, please consider tagging your DMS in the post.
Edit : You can get the members informations doing a subrequestion like this :
SELECT * FROM members WHERE id = (SELECT member_id FROM subscriptions WHERE CAST( GETDATE() AS Date ) NOT BETWEEN start_date AND end_date;);
wich will look for the members having the same id as the result of the ids in the previous request
答案2
得分: 1
因为您显然可以为任何给定的会员拥有多个订阅，所以您不能仅通过查看单个行来确定。
select member_id,
case when min(end_date) > '2023-07-31'
then min(start_date)
else max(case when start_date < '2023-07-31'
then start_date end) end as s_dt,
case when min(start_date) > '2023-07-31'
then min(end_date)
else max(case when start_date < '2023-07-31'
then end_date end) end as e_dt
from subscriptions
where member_id not in (
select member_id from subscriptions
where '2023-07-31' between start_date and end_date
)
group by member_id;
这假定结束日期包括在有效日期范围内，并且没有奇怪的重叠订阅范围。您可能可以使用公共表表达式（CTE）来避免重复使用相同的日期值。查看链接的演示示例。
您的示例中有一种情况，所有订阅都在参考日期之后，而您似乎希望返回最早的日期值，而不是最新的日期值。附加的逻辑正在寻找这种情况，而默认行为是仅查看已结束的订阅。
链接：https://www.db-fiddle.com/f/n4jPDZwLAyRa4Q9vHMPvnx/1
英文:
Because you can apparently have multiple subscriptions for any given member you won't be able to make the determination by looking only at individual rows.
select member_id,
case when min(end_date) > '2023-07-31'
then min(start_date)
else max(case when start_date < '2023-07-31'
then start_date end) end as s_dt,
case when min(start_date) > '2023-07-31'
then min(end_date)
else max(case when start_date < '2023-07-31'
then end_date end) end as e_dt
from subscriptions
where member_id not in (
select member_id from subscriptions
where '2023-07-31' between start_date and end_date
)
group by member_id;
This assumes that the end date is included in the range of valid dates and that you don't have any weird overlapping subscription ranges. You can probably use a CTE to avoid repeating the same date value over and over. See the linked fiddle for an example of that.
Your sample does have a case where all of the subscriptions follow the reference date and you seem to want the earliest date values to be returned rather than the latest. The additional logic is looking for such a case whereas the default behavior is to look only at subscriptions that had already ended.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论