如何在SQL中针对最新记录判断指定客户密码是否过期？

背景与问题 #

现有密码历史表数据：

需求是检查指定客户（比如ClientId=1）的最新设置密码是否过期，规则为创建时间超过90天即算过期。

原SQL语句如下，但它错误返回了1——因为匹配到了旧的过期密码记录（2022年8月11日那条），而不是针对最新的密码记录判断：

SELECT TOP 1 1 -- Returns 1 if password has expired
FROM PASSWORD_HISTORY CHP
WHERE (DATEDIFF(DAY, CHP.CreationDate, GETDATE())) > 90
AND CHP.ClientId = 1
ORDER BY CHP.CreationDate DESC

问题出在原语句的逻辑顺序：先筛选出该客户所有过期的密码记录，再取最新的一条，完全忽略了最新的未过期记录，导致误判。

解决方案 #

方法1：先取最新记录再判断 #

先通过子查询拿到指定客户的最新密码创建日期，再筛选这条记录并判断是否过期：

SELECT 1 AS IsExpired
FROM PASSWORD_HISTORY CHP
WHERE CHP.ClientId = 1
AND CHP.CreationDate = (
    SELECT MAX(CreationDate)
    FROM PASSWORD_HISTORY
    WHERE ClientId = 1
)
AND DATEDIFF(DAY, CHP.CreationDate, GETDATE()) > 90

• 若最新密码过期，返回1；未过期或无该客户记录时，返回空结果集。

方法2：用窗口函数标记最新记录 #

使用ROW_NUMBER()给每个客户的记录按创建日期倒序排名，取排名第一的最新记录做判断：

SELECT CASE 
         WHEN DATEDIFF(DAY, CreationDate, GETDATE()) > 90 THEN 1 
         ELSE 0 
       END AS IsExpired
FROM (
    SELECT 
        CreationDate,
        ROW_NUMBER() OVER (PARTITION BY ClientId ORDER BY CreationDate DESC) AS rn
    FROM PASSWORD_HISTORY
    WHERE ClientId = 1
) t
WHERE rn = 1

• 无论是否过期，都会明确返回1（过期）或0（未过期），结果更清晰。

方法3：调整TOP 1逻辑顺序 #

先取出该客户最新的一条密码记录，再判断是否过期：

SELECT CASE 
         WHEN DATEDIFF(DAY, CHP.CreationDate, GETDATE()) > 90 THEN 1 
         ELSE 0 
       END AS IsExpired
FROM (
    SELECT TOP 1 CreationDate
    FROM PASSWORD_HISTORY
    WHERE ClientId = 1
    ORDER BY CreationDate DESC
) CHP

• 逻辑直接，同样返回明确的1或0结果。

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