如何用SQL筛选无有效订阅的会员并获取其最新到期订阅信息

要解决这个问题，我们可以分两步处理：先排除拥有有效订阅的会员，再为剩余会员获取他们最新到期的订阅记录。以下是两种实用的SQL实现方案：

方案一：窗口函数标记最新订阅 + 排除有效会员 #

通过窗口函数为每个会员的订阅按到期日期倒序排序，标记出最新的那条；同时筛选出有有效订阅的会员ID，最后排除这些会员并关联最新订阅信息。

WITH latest_subs AS (
    SELECT 
        member_id,
        start_date,
        end_date,
        -- 按会员分组，到期日期最晚的订阅标记为1
        ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY end_date DESC) AS row_num
    FROM subscriptions
),
valid_member_ids AS (
    -- 提取所有拥有有效订阅的会员ID
    SELECT DISTINCT member_id
    FROM subscriptions
    WHERE start_date < '2023-07-31' 
      AND end_date > '2023-07-31'
)
SELECT 
    m.id AS member_id,
    m.name AS member_name, -- 请根据实际表字段调整
    ls.start_date AS latest_sub_start,
    ls.end_date AS latest_sub_end
FROM members m
LEFT JOIN latest_subs ls 
    ON m.id = ls.member_id 
    AND ls.row_num = 1 -- 只取最新的订阅
WHERE m.id NOT IN (SELECT member_id FROM valid_member_ids)
-- 若要排除从未订阅过的会员，添加：AND ls.member_id IS NOT NULL
ORDER BY m.id;

方案二：聚合函数获取最新到期日期 + 关联订阅详情 #

先通过聚合函数拿到每个会员的最新到期日期，再关联回订阅表获取对应的起始日期，同时排除有有效订阅的会员。

WITH member_latest_end AS (
    -- 获取每个会员的最新到期日期
    SELECT 
        member_id,
        MAX(end_date) AS latest_end_date
    FROM subscriptions
    GROUP BY member_id
),
valid_member_ids AS (
    SELECT DISTINCT member_id
    FROM subscriptions
    WHERE start_date < '2023-07-31' 
      AND end_date > '2023-07-31'
)
SELECT 
    m.id AS member_id,
    m.name AS member_name, -- 请根据实际表字段调整
    s.start_date AS latest_sub_start,
    s.end_date AS latest_sub_end
FROM members m
WHERE m.id NOT IN (SELECT member_id FROM valid_member_ids)
LEFT JOIN member_latest_end mle 
    ON m.id = mle.member_id
LEFT JOIN subscriptions s 
    ON mle.member_id = s.member_id 
    AND mle.latest_end_date = s.end_date
ORDER BY m.id;

补充说明 #

• 如果会员从未有过订阅记录，上述SQL中latest_sub_start和latest_sub_end会返回NULL，若要排除这类会员，可在WHERE子句中添加mle.member_id IS NOT NULL
• 建议根据实际表结构调整字段名（比如会员ID可能是member_id而非id，会员名称字段可能是username等）
• 若要避免NOT IN遇到NULL值的问题，可改用NOT EXISTS替换WHERE子句：

  WHERE NOT EXISTS (
      SELECT 1
      FROM subscriptions s
      WHERE s.member_id = m.id
        AND s.start_date < '2023-07-31'
        AND s.end_date > '2023-07-31'
  )
  

内容的提问来源于stack exchange，提问作者Burak Er