查询订阅表：按PlanType返回单条最大到期日期记录的SQL问题

我正在构建一个存储过程，用于查询订阅表以找出即将到期（例如7天后）的记录。因存在多种订阅计划，需按PlanType分组，每组仅返回一条拥有最大到期日期的记录。但当前查询返回了多条到期日期大于查询日期的记录，例如PlanType为"3"的两条记录，我只需要其中到期日期最晚的那条，以便与“今日”对比判断是否在7天到期范围内。

当前查询结果 #

SubscriptionId  AccountID   PlanType    Expiration              IsVerified  IsCanceled  IsActive
174322          5025132     2           2025-01-21 23:59:59.000 1           0           1
167756          5025132     3           2024-02-05 00:00:00.000 1           0           1
172026          5025132     3           2025-02-05 00:00:00.000 1           0           1

当前SQL代码 #

SELECT TOP (100) PERCENT
       SubscriptionId,
       AccountID,
       PlanType,
       MAX(ExpiresUtc) AS Expiration,
       --PendingExpiration7DayReminderSent,
       IsVerified,
       IsCanceled,
       IsActive
FROM dbo.Subscriptions
GROUP BY SubscriptionId,
         PlanType,
         AccountID,
         PendingExpiration7DayReminderSent,
         IsVerified,
         IsCanceled,
         IsActive
HAVING (
    --(MAX(ExpiresUtc) < DATEADD(d, 14, GETUTCDATE()))
    --AND 
    --(
    MAX(ExpiresUtc) > DATEADD(d, 7, GETUTCDATE())
       --)
       )
       AND
       (
           --(PendingExpiration7DayReminderSent = 1)
           --AND 
           (IsVerified = 1)
           AND (IsCanceled = 0)
           AND (IsActive = 1)
       )
       AND AccountID = 5025132 ORDER BY PlanType;

解决方案 #

你的核心问题是GROUP BY里包含了SubscriptionId——每个订阅ID都是唯一的，导致同一个PlanType下的不同订阅被拆成独立分组，自然返回多条记录。下面提供两种可行的解决方式：

方法1：用ROW_NUMBER()窗口函数（推荐） #

直接通过窗口函数给每个PlanType下的记录按到期日倒序排名，取排名第一的记录即可：

WITH RankedSubscriptions AS (
    SELECT 
        SubscriptionId,
        AccountID,
        PlanType,
        ExpiresUtc AS Expiration,
        IsVerified,
        IsCanceled,
        IsActive,
        -- 按PlanType分组，到期日最晚的排第1
        ROW_NUMBER() OVER (PARTITION BY PlanType ORDER BY ExpiresUtc DESC) AS RowNum
    FROM dbo.Subscriptions
    WHERE 
        IsVerified = 1 
        AND IsCanceled = 0 
        AND IsActive = 1 
        AND AccountID = 5025132
        -- 筛选未来7天内到期的记录（原代码逻辑是>7天后，和“即将到期”不符，按需调整）
        AND ExpiresUtc BETWEEN GETUTCDATE() AND DATEADD(d, 7, GETUTCDATE())
)
SELECT 
    SubscriptionId,
    AccountID,
    PlanType,
    Expiration,
    IsVerified,
    IsCanceled,
    IsActive
FROM RankedSubscriptions
WHERE RowNum = 1
ORDER BY PlanType;

方法2：先分组找最大到期日再关联 #

先通过分组获取每个PlanType的最大到期日，再关联原表拿到对应订阅的完整信息：

SELECT 
    s.SubscriptionId,
    s.AccountID,
    s.PlanType,
    s.ExpiresUtc AS Expiration,
    s.IsVerified,
    s.IsCanceled,
    s.IsActive
FROM dbo.Subscriptions s
INNER JOIN (
    SELECT 
        PlanType,
        MAX(ExpiresUtc) AS MaxExpiration
    FROM dbo.Subscriptions
    WHERE 
        IsVerified = 1 
        AND IsCanceled = 0 
        AND IsActive = 1 
        AND AccountID = 5025132
        AND ExpiresUtc BETWEEN GETUTCDATE() AND DATEADD(d, 7, GETUTCDATE())
    GROUP BY PlanType
) AS maxExp ON s.PlanType = maxExp.PlanType AND s.ExpiresUtc = maxExp.MaxExpiration
WHERE 
    s.IsVerified = 1 
    AND s.IsCanceled = 0 
    AND s.IsActive = 1 
    AND s.AccountID = 5025132
ORDER BY s.PlanType;

额外注意 #

• 原代码的MAX(ExpiresUtc) > DATEADD(d, 7, GETUTCDATE())逻辑是筛选到期日在7天之后的记录，和“即将到期”的需求不符，建议改成BETWEEN GETUTCDATE() AND DATEADD(d,7,GETUTCDATE())来覆盖未来7天内到期的范围。
• 如果同一个PlanType下有两条记录到期日相同且都是最大值，ROW_NUMBER()会随机返回一条；若要保留所有这类记录，把ROW_NUMBER()换成RANK()即可。

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