SQL Server单查询优化：优先取当前生效子记录，无则取最新

场景与需求 #

我们有两个SQL Server表：dbo.parent和dbo.child，二者为一对多关系（一个父项对应多个子项）。每个子项拥有唯一名称和effectiveTimestamp字段。

原有查询可接收指定的parentid数组，为每个父项从dbo.child中获取当前生效的子记录——即选取effectiveTimestamp小于等于当前日期时间的最大值对应的子项，该逻辑在常规场景下运行正常。

但存在特殊场景：部分父项的所有子项effectiveTimestamp均为未来日期（大于当前时间），此时原查询不会返回这些parentid的结果。目前需通过两次查询处理该情况，现要求用单查询实现：若父项无生效子记录，则获取effectiveTimestamp最大的子项（无论该时间是否早于当前时间）。

解决方案（ROW_NUMBER()实现） #

通过ROW_NUMBER()函数的分组排序逻辑，可一次性满足需求：

SELECT parentid,
       child.id,
       name,
       child.effectivetimestamp
FROM   dbo.child AS child
       INNER JOIN (SELECT *
                   FROM   (SELECT *,
                                  Row_number()
                                    OVER(
                                      partition BY parentid
                                      ORDER BY CASE WHEN effectivetimestamp <= Getdate() THEN 0
                                                    ELSE 1 END,
                                               effectivetimestamp DESC) AS rnk
                           FROM   dbo.child) AS c
                   WHERE  parentid IN ( 1, 2, 3, 4, 5 )
                          AND rnk = 1) AS effectiveVersion
               ON child.id = effectiveVersion.id 

逻辑说明 #

• 内层子查询通过PARTITION BY parentid按父项分组
• 排序规则中，用CASE语句将effectiveTimestamp<=当前时间的子项标记为0，未来时间的标记为1，确保生效记录优先排在前面
• 同一优先级内按effectiveTimestamp倒序排列，保证取到最新的记录
• 最终筛选每个分组中rnk=1的记录，即可实现：有生效子项时取最新生效项，无生效子项时取最新的未来子项

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